We have all been in a situation where we had to get the last day of the month with T-SQL. The world would just be so much easier with 30 days in every month, which is not the case. To calculate if the last day is 31, 30, 28 or maybe even 29 is not that easy. There are many solutions to this problem out there, here is the one that i normally use:

DECLARE @myDate Datetime = '2012-02-12'
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @myDate) +1, 0))

This code finds the difference in months, adds one month (this gives us the first day of the next month) and then substracts one day. And the result:

The last day of february next year is the 29th.

With the introduction of SQL Server Denali, we get a new function called EOMONTH. The EOMONTH takes two parameters the first one being the date and the next optional one being the number of months to add to the start date.

EOMONTH (start_date [, month_to_add]

Let’s have a look at how my code now will look:

DECLARE @myDate Datetime = '2012-02-12'
SELECT EOMONTH (@myDate) AS Result;

EOMONTH returns a type of start_date or the datatype datetime2.

Now I started searching BOL like crazy to find the BOMONTH function, but no no that is not to be found, yet? Maybe somebody should create a connect issue with Microsoft on that. But anyway by using the EOMONTH function it is also pretty easy to get the first day of the month, here is the code:

DECLARE @myDate Datetime = '2012-02-12'
SELECT DATEADD(day, 1, EOMONTH(@myDate, -1)) as FirstDayOfMonth

This is not the biggest new feature in SQL-Server Denali, but anyway it saves me lines of code, so I like it.

@ms1333