End Of Month Function in Denali CTP 3

While playing around with Denali, I came across a function that I believe should have been included in SQL for years. It is called EOMONTH and it “returns the last day of the month that contains a specified date”. You can pass in any date value and it will return the date of the last day of that month. Listing 1 demonstrates two ways to use this new function. Figure 1 shows results of executing Listing 1 on SQL Server Denali CTP 3.

Listing 1: New EOMONTH TSQL Function

SELECT EOMONTH('2/14/2010') AS EndOfFebruary

--OR

SELECT EOMONTH(GETDATE()) AS EndOfCurrentMonth

Figure 1: Results of Listing 1

We could determine the end of the month fairly easily in prior versions, but why should we when it would be so easy for MS to add it to their list of built in functions? Also note, that they still haven’t added one for Beginning of Month or others such as Beginning and End of Quarter, etc… BOMonth would be pretty easy to create as it is always the first day of the months. So, one could easily create a function to do this and add it to the master db. BOQuarter and EOQuarter wouldn’t too bad either, but again, you’ll need to do this yourself for now. I wish MS would go ahead and add these by default.

And if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me using any of the below methods.