Function to calculate end of month - SQL Server 2012 - new function EOMonth

As in my previous blog posts, I've been posting some new features in SQL Server 2012. In today's post, I would like to show you a new T-SQL function wich will lead you to solve a daily problem.

So far, when you needed to get the last day from current month you would do something like example below:

select dateadd(MONTH,1,convert(datetime,left(convert(varchar,GETDATE(),112),6)+'01'))-1

Result: 2012-04-30 00:00:00

which in other words, these are the steps we are doing:
- convert(varchar,GETDATE(),112)   -   convert date to YYYYMMDD format

- left(convert(varchar,GETDATE(),112),6)+'01'    -   get the year-month part and concatenate with 01 to get the 1st day of month

- dateadd(MONTH,1,convert(datetime,left(convert(varchar,GETDATE(),112),6)+'01'))   -   add 1 month to current date to get the 1st day of the next month

- and finally -1 so we can get the last day of current month.

This is now much easier with EOMonth (End-of-month) function. Below is the syntax:

EOMONTH ( start_date [, month_to_add ] )

 SELECT EOMONTH(GETDATE())

and we get the same result 2012-04-30 00:00:00.000

If you need to identify the last day from next month, all you need to do is:

SELECT EOMONTH (GETDATE(),1)

Result: 2012-05-31 00:00:00

More info in BOL.