EOMONTH()
EOMONTH(): This function will return last day (full date) of the month for which date is specified. It takes date as parameter with offset (for next or previous months) as an optional parameter.
Syntax of EOMONTH():
EOMONTH ( start_date [, month_to_add ] )
Example of EOMONTH():
DECLARE @current_date DATETIME
SET @current_date ='10/1/2012'
SELECT EOMONTH( @current_date )AS End_Date;
Output will be 2012-10-31.
With Offset:
DECLARE @current_date DATETIME
SET @current_date ='10/1/2012'
SELECT EOMONTH( @current_date )AS Curr_Date;
SELECT EOMONTH( @current_date, 1 )AS Next_Month_Date;
SELECT EOMONTH( @current_date,-1 )AS Last_Month_Date;
The output will be 2012-10-31 as Curr_date, 2012-11-30 as Next_Month_Date and 2012-09-30 as Last_Month_Date.
For complete post go to SQL Server 2012 Enhancements.