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())

2011-07-31 00:00:00.000

If you pass in a date, you will also get the last date for that month

SELECT EOMONTH('20110615')

2011-06-30 00:00:00.0000000

This function also accepts an optional parameter: month_to_add

_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)

2011-07-31 00:00:00.0000000

If we pass -1 for month_to_add it will subtract a month

SELECT EOMONTH('20110615',-1)

2011-05-31 00:00:00.0000000

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.