SQL Server Denali CTP3 has a bunch of new date/time functions like DATEFROMPARTS, DATETIMEFROMPARTS and EOMONTH
First let's take a look at EOMONTH.
The syntax for EOMONTH is
EOMONTH ( start_date [, month_to_add ] )
If you pass in getdate() you will get the last day of the month for the current month
- SELECT EOMONTH(getdate())
If you pass in a date, you will also get the last date for that month
- SELECT EOMONTH('20110615')
This function also accepts an optional parameter: month_to_add
Optional integer expression specifying the number of months to add to start_date.
If this argument is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised.
So if we pass 1 for month_to_add it will add a month
- SELECT EOMONTH('20110615',1)
If we pass -1 for month_to_add it will subtract a month
- SELECT EOMONTH('20110615',-1)
The one problem with this function is that if you do a query and specify between some date and EOMONTH it won't give you anything after midnight. I already explained that in this post: How Does Between Work With Dates In SQL Server?
I also wonder why there is no SOMONTH function? Yes, I know it starts with 1, but if there is an end of month function then someone will also search for a start of month function.