Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Last Day of the Month Function (EOMONTH)
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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]

Back to Tip of the Day List Next Tip