Get the last day of the month in SQL

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)))

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)

Related