SQL Server 2012 introduces a new date function called EOMONTH which returns the last day of the month that contains the specified date, with an optional offset.
EOMONTH ( <start_date> [, <month_to_add> ] )
The <start_date> argument is a date expression specifying the date for which to return the last day of the month. The optional argument <month_to_add> is an integer expression specifying the number of months to add to <start_date>. If the <month_to_add> is specified, then the EOMONTH date function adds the specified number of months to <start_date>, and then returns the last day of the month for the resulting date. If this addition of the <month_to_add> to the <start_date> overflows the valid range of dates, then an error is raised.
From SQL Server 2008 R2, SQL Server 2008 and below (that is, SQL Server 2005 and SQL Server 2000), this function can easily be replicated using the following statement:
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()), 0) - 1 AS [LastDayOfMonth]
Simply replace the GETDATE() function in this statement with any <start_date> value and it will return the last day of the month that contains that specified date. As to including the <months_to_add> argument, the statement can be modified as follows:
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + <months_to_add>, 0) - 1 AS [ResultDate]
Using this statement, to get the last day of the previous month and the last day of the following month, the <month_to_add> will be replace by -1 and 1 respectively:
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, 0) - 1 AS [LastDayOfPreviousMonth]
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + 1, 0) - 1 AS [LastDayOfFollowingMonth]