Search code examples
sqlsql-servert-sql

Get the last day of the month in SQL

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?

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.