EOMONTH (Transact-SQL)

This new function in SQL Server 2012 returns the last day of the month that contains the specified date, with an optional offset. This used to be a more difficult in earlier versions of SQL Server using a variety of functions to get to the same answer as just this one statement.

DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO

Here is the result set.

This Month
-----------------------
2014-01-31

(1 row(s) affected)

Next Month
-----------------------
2014-02-28

(1 row(s) affected)

Last Month
-----------------------
2013-12-31

(1 row(s) affected)