Home > SQL Server 2012, SQL Server 2012 Date Functions > How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared

How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared


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 !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: