EOMONTH() Equivalent in SQL Server 2008 R2 and below


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)

Comments (10)

  1. msn4free.com says:

    Technical blog covering Microsoft SQL Server, SSMS, T-SQL, database testing, & testing concepts

  2. Patrick says:

    That was not so complicated:

    select dateadd(day, -day(@date), dateadd(month,1,@date) )

  3. Patrick says:

    Actually, does not work. Sorry!

  4. LeRoy Valley says:

    EOMONTH is available in SQL 2008 R2 as well…

  5. Kate says:

    LeRoy, it is not available in 2008R2 — msdn.microsoft.com/…/hh213020(v=sql.110).aspx no "other versions" available from the drop down.

  6. Floyd Johnson says:

    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]

  7. Floyd Johnson says:

    –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]

  8. Eslam Abd Elbaaset says:

    Thank you very much

  9. Wasantha says:

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