Freigeben über

EOMONTH() Equivalent in SQL Server 2008 R2 and below

  • Artikel

 

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:

EOMONTH() Equivalent in SQL Server 2008 R2

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:

 

EOMONTH() Equivalent in SQL Server 2008 R2

 

Hope it helps,
Sam Lester (MSFT)

Comments

  • Anonymous
    September 23, 2013
    Technical blog covering Microsoft SQL Server, SSMS, T-SQL, database testing, & testing concepts

  • Anonymous
    October 26, 2013
    Nice!

  • Anonymous
    January 20, 2014
    That was not so complicated: select dateadd(day, -day(@date), dateadd(month,1,@date) )

  • Anonymous
    January 20, 2014
    Actually, does not work. Sorry!

  • Anonymous
    April 18, 2014
    EOMONTH is available in SQL 2008 R2 as well...

  • Anonymous
    December 02, 2014
    LeRoy, it is not available in 2008R2 -- msdn.microsoft.com/.../hh213020(v=sql.110).aspx no "other versions" available from the drop down.

  • Anonymous
    April 02, 2015
    select convert(datetime,convert(date,dateadd(dd,-(day(dateadd(mm,-1,getdate()))),dateadd(mm,-1,getdate())),100),100) [EOM Prior Month] select convert(datetime,convert(date,dateadd(dd,-(day(dateadd(mm,1,getdate()))),dateadd(mm,1,getdate())),100),100) [EOM Current Month] select convert(datetime,convert(date,dateadd(dd,-(day(dateadd(mm,2,getdate()))),dateadd(mm,2,getdate())),100),100) [EOM Next Month]

  • Anonymous
    April 02, 2015
    --Correction for [EOM Prior Month] select convert(datetime,convert(date,dateadd(dd,-(day(dateadd(mm,0,getdate()))),dateadd(mm,0,getdate())),100),100) [EOM Current Month]

  • Anonymous
    July 22, 2015
    Thank you very much

  • Anonymous
    June 08, 2016
    declare @year numeric;declare @month numeric;set @year=2016;set @month=3;select DATEADD(dd,-1,DATEADD(MONTH,1,Convert(DATE,('01'+'-'+cast(@month as varchar(2))+'-'+CAST(@year as varchar(4))),103))) ;