Get the last day of the month in SQL
sql, sql-server, t-sql
Solution
Here's my version. No string manipulation or casting required, just one call each to the `DATEADD`, `YEAR` and `MONTH` functions:
DECLARE @test DATETIME
SET @test = GETDATE() -- or any other date
SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)
This works by taking the number of months that have passed since the initial date in SQL's date system (1/1/1990), then adding that number of months to the date "-1" (12/31/1899) using the DATEADD function, which returns a proper datetime at the corresponding month's end (that of the date you specified). It relies on the fact that December always ends on the 31st and there are no months longer than that.
Problem
I need to get the last day of the month given as a date in SQL. If I have the first day of the month, I can do something like this: ``` DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1) ``` But does anyone know how to generalize it so I can find the last day of the month for any given date?