SQL Server 2012 introduced several new system functions pertaining to the date and time data types. In my opinion, I’ve found the EOMONTH(), “end of month”, function to be one of the most useful. The EOMONTH() function returns the final day of the calendar month, thus greatly simplifying a common calculation, taking into account differing number of days in each month, the leap year calculation, etc..
For SQL Server 2012:
I’ve started to rely on this function a bit and recently needed to use a similar calculation in SQL Server 2008 R2. I came across a nice thread in the MSDN forums showing several creative ways to accomplish this here. Below are a few of the possible solutions, with the original author referenced in the comments. Notice that the output format varies slightly between all four solutions. Feel free to post any additional ways you’ve solved it in the past.
For SQL Server 2008 R2 and lower:
Hope it helps, Sam Lester (MSFT)