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:
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 ] )
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.