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




Figure 1: Results of Listing 1

EOMONTH Denalit CTP3 Results brian k mcdonald sqlbigeek


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.




sqlbigeek brian k mcdonald


Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant

Email: bmcdonald@SQLBIGeek.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN Articles | BIDN Blogs | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald