| Downloads |
|---|
| 23903.zip |
How can I use T-SQL to determine whether a date is the last day of the month?
T-SQL doesn't have particularly rich date processing. But to solve your problem, you can use the DATEDIFF() function, which returns the number of date and time boundaries crossed between two dates:
DATEDIFF
( datepart , startdate , enddate )
A date is the last day of the month if, when you add one day to the date, it changes to a new month. Imagine a test date called @InputDate. You can add one day to @InputDate, then use the DATEDIFF() function to determine whether you've crossed a datetime boundary.
The two batches in Listing 1 illustrate how to implement this logic. The first batch returns 0, which indicates that you didn't cross a month-based date boundary. Thus, @InputDate wasn't the last day of the month. The second batch returns 1, which indicates that you did cross a month-based date boundary. If you want to implement the DATEDIFF() test in a production system, you can take this example one step further by implementing the check inside a scalar-valued user-defined function (UDF). You can then easily reuse the test. The script in Listing 2 shows how you can create and use this test as a UDF.
Microsoft introduced UDFs in SQL Server 2000. Many developers don't use them regularly, but I rely on them extensively. For an introduction to UDFs, see Robert Vieira, "User-Defined Functions," November 2000, InstantDoc ID 15544.
