SQL SERVER – Find Last Day of Any Month – Current Previous Next

Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.

I have answered this question twice before here:

SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month

Today, we will see the same solution again. Please use the method you find appropriate to your requirement.

Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000

LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000

LastDay_NextMonth
———————–
2007-09-30 23:59:59.000

If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth

ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop
Next Post
SQLAuthority News – Book Review – Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes

Related Posts

288 Comments. Leave new

  • Marcos Marcon
    July 27, 2015 5:34 pm

    Thanks man, it was very usefull for me!

    Reply
  • Written 8 years ago and saved me a lot of time today. Many thanks!

    Reply
  • Thanks a lot I got my solution.
    I wrote the following query.

    select convert(datetime, cast(subs_to_yr as nvarchar) + right( (‘0’+LTRIM(RTRIM(subs_to_mth)) ),2) + ’01’) as c1 , subs_to_yr, subs_to_mth from MEM_DEP_ENTRY where subs_to_yr is not null and Subs_To_Mth > 0

    Reply
  • Update MEM_DEP_ENTRY set Validdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, convert(datetime, cast(subs_to_yr as nvarchar) + right( (‘0’+LTRIM(RTRIM(subs_to_mth)) ),2) + ’01’) )+1,0)) where validdate is null and subs_to_mth > 0 and Subs_To_Yr is not null

    Reply
  • Hi All,

    If we have only values coming in months like Jan, Feb , Mar etc.
    How can I find Last date of every month w.r.t a month respectively

    Reply
  • How to fetch data from data base from last three days(29-02-2016 to 3-03-2016, if second march is not having any data

    Reply
  • Thank you! You give great tips…this one included!

    Reply
  • SELECT EOMONTH(GETDATE()) can also be used to get the last day of current date. :)

    Reply
  • I love this blog! Thank you Mr. Pinal
    I’ve put together the below to try to explain how dates work. Everything stems from the fact that, in SQL terms, the beginning of time was 01-01-1900.

    I add / subtract days rather than seconds as this gives a date without time so I can use ” date =” in my queries.

    I’ve added the EOMONTH function which is only available in later versions of SQL.

    Hope this helps someone.

    DECLARE @TestDate DATETIME
    SET @TestDate = ‘2016-11-15’

    SELECT
    ‘The beginning of time’ = CONVERT(DATETIME,0),
    ‘Tomorrow’ = @TestDate + 1,
    ‘Months since time began plus one’ = DATEDIFF(m,0,@TestDate)+1, –=1403
    ‘First Day of Next Month with Months int’ = DATEADD(m,1403,0)
    –(add 1403 months to the beginning of time)
    SELECT
    ‘First Day of Next Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+1,0),
    ‘Last Day of This Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+1,0)-1,
    ‘First Day of Last Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)-1,0),
    ‘Last Day of Last Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate),0)-1,
    ‘Last Day of Next Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+2,0)-1
    SELECT
    ‘First Day of Next Month’ = CONVERT(DATETIME,EOMONTH(@TestDate))+1,
    ‘Last Day of This Month’ = CONVERT(DATETIME,EOMONTH(@TestDate)),
    ‘First Day of Last Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-2))+1,
    ‘First Day of This Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-1))+1,
    ‘Last Day of Last Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-1)),
    ‘Last Day of Next Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,1))

    Reply
  • I am supplying @month and @year anyway to pull the last day of the month and year provided?

    Reply
  • Nice post Dave. Really helpful. Thanks.

    Reply
  • i have data firstdate = ‘2010/10/25’ and second = today(2017/09/04),
    how to preview like 6 Years, 10 months and 9 days…?
    example in excel we use
    DATEDIF(H9;$P$2;”Y”)&” Years “&DATEDIF(H9;$P$2;”YM”)&” Months “&DATEDIF(H9;$P$2;”MD”)&” Days”

    how to preview in sql server.. please help me… tq

    Reply
    • Hello Breng qie,
      Try this. If your start date and end date are columns, you will need to create this as a function.

      DECLARE
      @StartDate DATETIME = ‘2010-10-25’,
      @EndDate DATETIME = ‘2017-09-04’,
      @Years INT,
      @Months INT,
      @Days INT

      IF MONTH(@StartDate) < MONTH(@EndDate)
      SELECT
      @Years = YEAR(@EndDate) – YEAR(@StartDate),
      @Months = MONTH(@EndDate) – MONTH(@StartDate)
      ELSE
      SELECT
      @Years = YEAR(@EndDate) – YEAR(@StartDate) – 1,
      @Months = MONTH(@EndDate) – MONTH(@StartDate) + 11

      IF DAY(@StartDate) < DAY(@EndDate)
      SET @Months = @Months + 1
      ELSE
      SET @Startdate = DATEADD(DAY,1,@StartDate)

      SELECT
      @StartDate = DATEADD(YEAR,@Years,@StartDate),
      @StartDate = DATEADD(MONTH,@Months,@StartDate),
      @Days = DATEDIFF(DAY,@StartDate, @EndDate)

      SELECT
      CONVERT(VARCHAR(10),@Years) + ' Years, '
      + CONVERT(VARCHAR(2),@Months) + ' Months and '
      + CONVERT(VARCHAR(2),@Days) + ' Days'

      Reply
    • Here’s the function. I’d be very interested if anyone has a more elegant way of doing this………?

      CREATE FUNCTION fn_GetDateCount
      (
      @StartDate DATETIME = ‘2010-10-25’,
      @EndDate DATETIME = ‘2017-09-04’
      )
      RETURNS VARCHAR(256)
      AS
      BEGIN
      DECLARE
      @Years INT,
      @Months INT,
      @Days INT,
      @Output VARCHAR(256)

      IF MONTH(@StartDate) < MONTH(@EndDate)
      SELECT
      @Years = YEAR(@EndDate) – YEAR(@StartDate),
      @Months = MONTH(@EndDate) – MONTH(@StartDate)
      ELSE
      SELECT
      @Years = YEAR(@EndDate) – YEAR(@StartDate) – 1,
      @Months = MONTH(@EndDate) – MONTH(@StartDate) + 11

      IF DAY(@StartDate) < DAY(@EndDate)
      SET @Months = @Months + 1
      ELSE
      SET @Startdate = DATEADD(DAY,1,@StartDate)

      SELECT
      @StartDate = DATEADD(YEAR,@Years,@StartDate),
      @StartDate = DATEADD(MONTH,@Months,@StartDate),
      @Days = DATEDIFF(DAY,@StartDate, @EndDate)

      SELECT @Output =
      CONVERT(VARCHAR(10),@Years) + ' Years, '
      + CONVERT(VARCHAR(2),@Months) + ' Months and '
      + CONVERT(VARCHAR(2),@Days) + ' Days'

      RETURN @Output
      END

      –To USE: SELECT dbo.fn_GetDateCount ('2010-10-25', '2017-09-28')
      — or with columns from a table SELECT dbo.fn_GetDateCount (StartDate,getdate()) FROM EMPS

      Reply
  • Last Day of Any Month and Year calculation in SQL saved my day, I needed this column to use in DAX expression in Tabular model to get sales value of the last day MTD for any given month.

    Reply
  • Germán Fajardo
    January 18, 2018 10:05 pm

    DECLARE @date DATETIME = ’12/1/2011′;
    SELECT EOMONTH ( @date ) AS Result;
    GO

    Reply
    • Blog was written in year 2007 when EOMONTH was not there in SQL. It was introduced in SQL 2012, I think.

      Reply
  • Thank you very much sir. useful since stuck on sql 2008 for now

    Reply
  • hello sir. thank you for the solution . But kindly describe the formula and logic for the same. as a fresher can understand that also.

    Reply
  • ;WITH monthstart
    AS (SELECT CONVERT (DATE, Dateadd(year, Datediff(year, 0, Getdate()), 0))
    AS
    [BeginDate]
    UNION ALL
    SELECT Dateadd(month, 1, [begindate])
    FROM monthstart
    WHERE [begindate] < CONVERT (DATE, Dateadd(month, -1, Dateadd(year,
    Datediff(year, 0,
    Getdate()) + 1
    , -1)))),
    monthend
    AS (SELECT Eomonth(CONVERT (DATE, Dateadd(year, Datediff(year, 0, Getdate()
    ), 0)))
    AS
    [LastDate]
    UNION ALL
    SELECT Eomonth(Dateadd(month, 1, [lastdate]))
    FROM monthend
    WHERE [lastdate] < CONVERT (DATE, Dateadd(year, Datediff(year, 0,
    Getdate()
    ) +
    1, -1)))
    SELECT Month([begindate]) AS [Month],
    [begindate],
    [lastdate]
    FROM monthstart
    JOIN monthend
    ON Month(begindate) = Month(lastdate)

    Reply
  • OMG a post written 13 years ago is still so helpful! Thanks Pinal!

    Reply

Leave a Reply

Menu