Tag: SQL Server Date Functions

SQL Server 2012 has a new Function called EOMONTH() which returns the last day of the month for the specified date in parameter and also has an option to define offset as well.

Let us first see How we can do this in SQL Server 2008

--Last Day of Last Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS [Last Day of Last Month]
--Last Day of This Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) AS [Last Day of Next Month]

Let us see How SQL Server 2012 simplifies the above code

--Last Day of Last Month
SELECT    EOMONTH(GETDATE(), -1) AS [Last Day of Last Month]
--Last Day of Current Month
SELECT    EOMONTH(GETDATE()) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    EOMONTH(GETDATE(), 1) AS [Last Day of Next Month]

In SQL Server 2008 we had to make 4 function calls in order get the output, whereas SQL Server 2012 helps us do the same thing in 2 function calls

We can use the same function to get First Day of the Month as well

--First Day of Last Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AS [First Day of Last Month]
--First Day of This Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS [First Day of This Month]
--First Day of Next Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE())) AS [First Day of Next Month]

Hope this helps !!!