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?

From SQL Server 2012 you can use the EOMONTH function.

Returns the last day of the month that contains the specified date, with an optional offset.

Syntax

EOMONTH ( start_date [, month_to_add ] )

How ... I can find the last day of the month for any given date?

SELECT EOMONTH(@SomeGivenDate)

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)

You could get the days in the date by using the DAY() function:

dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(date), date)))

Related