EOMONTH() Function Usage in SQL Server 2012 and On

The EOMONTH() function is new in SQL Server 2012.

BOL link: http://technet.microsoft.com/en-us/library/hh213020.aspx

In the previous version (SQL Server 2008), a popular albeit obscure way to get the end of the month:

SELECT CONVERT(DATE, dateadd(mm, datediff(mm,0, current_timestamp)+1,-1));

-- 2013-06-30

Using the new function which returns DATE:

SELECT EOMONTH(current_timestamp);

-- 2013-06-30

We can add an optional parameter to get the end date for other months:

SELECT EOMONTH(current_timestamp, +1); -- 2013-07-31

SELECT EOMONTH(current_timestamp, -1); -- 2013-05-31

Using a dynamic parameter, we can get the last day of previous year:

SELECT EOMONTH(current_timestamp, -MONTH(current_timestamp)); -- 2012-12-31

Applying the DATEADD function we can obtain the first day of current year:

SELECT DATEADD(DD, 1, EOMONTH(current_timestamp, -MONTH(current_timestamp))); -- 2013-01-01

Applying the DATEDIFF function we can calculate today's Julian date:

SELECT DATEDIFF(DD, EOMONTH(current_timestamp, -MONTH(current_timestamp)), current_timestamp);

-- 163

The first parameter can be local variable:

DECLARE @dt date = current_timestamp;

SELECT EOMONTH(@dt, -1);
-- 2013-05-31

 We can use EOMONTH() in a query: 

SELECT SalesOrderID, OrderDate, EOMONTH(OrderDate) AS MonthEnd

FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID;

/*

SalesOrderID OrderDate MonthEnd

....

43841 2005-07-31 00:00:00.000 2005-07-31

43842 2005-07-31 00:00:00.000 2005-07-31

43843 2005-08-01 00:00:00.000 2005-08-31

43844 2005-08-01 00:00:00.000 2005-08-31

....

*/