SQL Server Denali – EOMONTH()

EOMONTH() returns the date value that represents the last day of the month for given date.
Syntax: EOMONTH( start_date [, months_to_add] )
Before Denali, to calculate the date value that represents the last day of the month we used:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE()) AS LastDay1, EOMONTH('2011-09-05') AS LastDay2

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth, EOMONTH(GETDATE()) AS LastDayOfMonth, EOMONTH (GETDATE(),1)LastDayOfNextMonth