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

  • Thnks sir providing code for getting last date of previous month in sql server.

    Reply
  • I need a solution …
    Here are the details….
    Step a) I will select a month and year(eg. January 2012) from UI.
    Step b) It should return four months back data (only month number).
    for this purpose i am trying to use datediff(m,,) function, this is perfectly working for current year( from May to December month).

    but when i am selecting January month it is returning current years September month but i need to get last year’s September month. Same as for February, March, April month.

    Please help.

    Reply
  • for Last Date of previous month

    select DATEADD(D,-( DATEPART(D,GETDATE())),GETDATE())

    Reply
  • Devashish Das
    January 22, 2013 4:53 pm

    –Alternate method

    DECLARE @MyDate DATETIME
    SET @MyDate = ’26-Jul-2013′

    SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,-1,@MyDate))
    SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
    SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))

    Reply
    • Devashish Das
      January 22, 2013 4:59 pm

      –Alter Nate Method
      DECLARE @MyDate DATETIME
      SET @MyDate = ’26-Jul-2013′

      — Previous Month
      SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
      — Given Month
      SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))
      — Next Month
      SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,2,@MyDate))

      Reply
  • Thank you very much for your post !

    Reply
  • I want exact month end date so

    declare @date datetime
    set @date=’31-jan-2012′
    while @date<='31-jul-2012'
    Begin
    print @date
    set @date= DATEADD(M,1,@date)
    End
    but i'm getting result like
    Jan 31 2012 12:00AM
    Feb 29 2012 12:00AM
    Mar 29 2012 12:00AM
    Apr 29 2012 12:00AM
    May 29 2012 12:00AM
    Jun 29 2012 12:00AM
    Jul 29 2012 12:00AM
    Please help me to get exact month end date

    Reply
    • declare @date datetime
      set @date=’20120101′
      select dateadd(month,datediff(month,0,dates)+1,-1) as last_day from
      (
      select dateadd(month,number,@date) as dates from master..spt_values
      where type=’p’ and number between 0 and 6
      ) as t

      Reply
      • Just Create this Stored Procedure in a Data Base
        And call it
        ============================
        USE [TestDataBase]
        GO
        /****** Object: UserDefinedFunction [dbo].[LAST_DAY] Script Date: 11/08/2012 00:48:07 ******/
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        – =============================================
        – Author: Luis R. Vela Morales
        – Create date: 01/11/2012
        – Description: De una fecha dada, retorna la
        – fecha con el ultimo dia del mes,
        – esto decrementando, sin afectar o
        – incrementando en ‘n’ meses
        – ( -n, 0, n).
        – =============================================
        CREATE FUNCTION [dbo].[LAST_DAY]
        (
        – Add the parameters for the function here
        @Fecha datetime,
        @Intervalo int
        )
        RETURNS datetime
        AS
        BEGIN
        – Declare the return variable here
        DECLARE @Result datetime

        – Add the T-SQL statements to compute the return value here
        SELECT @Result = DATEADD(day,-day(DATEADD(mm, 1+@Intervalo,@Fecha)),DATEADD(mm, 1+@Intervalo,@Fecha))

        – Return the result of the function
        RETURN @Result
        END

  • Hi Dear,

    Very Important article of SQL Developers
    Thank you.

    Reply
  • Subir Sankar Das
    April 30, 2013 4:53 pm

    Last Year last month (if today is 30-Apr-2013, it should reflect 30-Apr-2012)

    Reply
  • Russell Cribb
    June 18, 2013 3:21 am

    SELECT EOMONTH(SYSDATETIME()) AS end_of_current_month;

    Reply
  • Can i get the only month and year of ther previous month in the FORMAT JUN-13

    Reply
  • Mrinalini Agrawal
    December 12, 2013 8:52 pm

    using the query,
    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0))
    LastDay_PreviousMonth,
    How can I get the date in yyyymmdd format ?

    Reply
  • Hi all, SQL Server 2012 has new Function EOMONTH and makes life easier:

    DECLARE @mydate DATETIME= GETDATE()
    SELECT EOMONTH(@mydate,1) AS [Last Day Of Next Month];
    SELECT EOMONTH(@mydate) AS [Last Day Of Current Month];
    SELECT EOMONTH(@mydate,-1) AS [Last Day Of Previous Month];
    SELECT DATEADD(dd,1,EOMONTH(@mydate)) AS [First Day Of Next Month];
    SELECT DATEADD(dd,1,EOMONTH(@mydate,-1)) AS [First Day Of Current Month];

    Reply
  • another way but slightly modified in sql 2012. I think it might be repetitive
    select dateadd(mm,0,getdate())-day(getdate())- to get previous month last date.
    select dateadd(mm,1,getdate())-day(getdate())-to get current month last date.
    select dateadd(mm,2,getdate())-day(getdate())-to get next month last date.
    depending upon what we need we can increase the second arguement.

    Reply
  • Great site. I think my request is simple, but I could not find it. My file is currently daily, and I want to reduce it to only the last day of all the months that the database provides. Similarly, want the last day, period, of the given item. Some items are current, and some expired several years ago.
    Thanks,
    Stephen

    Reply
  • I find the way to calculate the same.

    DECLARE @PARAMETER DATETIME,
    @MONTH VARCHAR(20),
    @YEAR VARCHAR(20)
    SELECT @MONTH = ‘FEBRUARY’,@YEAR = ‘2012’
    SELECT @PARAMETER = @MONTH +’ ‘+@YEAR
    DECLARE @DATE DATETIME
    SET @DATE = SUBSTRING(REPLACE(REPLACE(CONVERT(VARCHAR(19), @PARAMETER, 126),’-‘,”),’:’,”),1,8)
    SELECT DATEADD(DAY,-1,DATEADD(MM,1,@DATE)) [LAST DAY OF MONTH],@DATE [FIRST DAY OF MONTH]

    Reply
  • G-Soft Provide Smart Solutionsk
    November 3, 2014 6:37 pm

    Please solved issues – start date and end date should be different why….

    SELECT DATEADD(MONTH,-1,DATEADD(MONTH,1378,0))
    SELECT DATEADD(SECOND,-1,DATEADD(MONTH,1378,0))

    Reply
  • Great tips. Thx a lot.

    Reply
  • Great post, I would like just improve your function to get Last Day of Current Month
    you can use some thing look like this: (DATEADD(MONTH, 1, @ano_mes + ’01’) – 1)

    Reply
  • Sir Please Help Me
    function that will return all the previous month names of a particular year ,if date is passed to that function as an input parameter.

    For eg if date is passed as 06/08/2009 then output should be
    Jan
    Feb
    March April
    May
    June
    July
    Aug

    thanx in advance

    Reply
    • Prashant – I replied for another comment with same question.
      It would be better to create table with month names and use that to get the desired output.

      Reply
  • Thanks for great blog.
    How do I calculate for last day of previous 6 months?

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version