Intro
As we all are waiting in excitement for the final release of SQL Server “Denali” I’m going to look at the new TSQL functions it brings for developers. The first function is EOMONTH. EOMONTH stand for “End Of MONTH”.
MSDN Description
Returns the last day of the month that contains the specified date, with an optional offset.
Syntax Format: EOMONTH ( start_date [, month_to_add ] )
Start date specify the date for which to return the last day of the same month. Month to add is an optional parameter to add months to the specified start date.
TSQL Example
Basic example for EOMONTH function:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date) AS Result; --Result: 2011-08-31 00:00:00.000

As you can see when I specify the 10 August 2011 the last date of the month is 31 August 2011. You can specify the start date as DATETIME or as a VARCHAR value. EOMONTH will do a implicit conversion of the VARCHAR to DATETIME.

Example to get next month last date:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date, 1) AS Result; --Result: 2011-09-30 00:00:00.000

Now by adding the optional parameter of 1 to the EOMONTH function you ask for the next month last date. As the example shows the result is 30 September 2011 for the input of 10 August 2011. If I change it to 2 then I will get the second month after the specified date.

Example to get previous month last date:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date, -1) AS Result; --Result: 2011-07-31 00:00:00.000

This example show how to get the previous month last date from the specified input date. You simply make the optional parameter minus. The input was again 10 August 2011 and the result for EOMONTH function was 31 July 2011.

Practical Example

My practical examples are using the AdventureWorks2008R2 database for Denali which is available here. Let look at a practical example for EOMONTH function:

DECLARE @begin_date DATETIME = '01/01/2007',
        @end_date DATETIME;
SELECT  @end_date = EOMONTH(@begin_date, 3); --Result: 2007-04-30 00:00:00.000

SELECT SUM(TotalDue) AS 'First Quarter Purchase Total' --Result: 145291.7959 
  FROM [AdventureWorks2008R2].[Purchasing].[PurchaseOrderHeader]
 WHERE OrderDate > @begin_date
   AND OrderDate < @end_date

In this example I calculate the first quarter purchase total for the year 2007. I specify a start date and use the EOMONTH function to calculate the last date of the quarter. Now it is easy to just sum up the TotalDue column for records between the begin date and end date.

As you can see the EOMONTH function makes it allot easier to get the last day of a month and it helps to make your TSQL code smaller and more understandable. Let me know what you think about this function addition in SQL Server 2011 Denali or if you have any questions.

Cheerio!