locked
First and Last day of previous month from getdate() RRS feed

  • Question

  • Hi All,

    How to get First day of previous month and last day of previous month(From getdate()) using SQL..?

     

     


    vineesh1701
    • Moved by Tom Phillips Friday, December 10, 2010 1:32 PM TSQL Question (From:SQL Server Database Engine)
    Friday, December 10, 2010 12:00 PM

Answers

  • Short and simple...

    SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS FirstPreviousMonth,
    	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS LastPreviousMonth
    
    • Proposed as answer by Naomi N Friday, December 10, 2010 3:10 PM
    • Marked as answer by Kalman Toth Wednesday, December 15, 2010 2:00 PM
    Friday, December 10, 2010 2:10 PM

All replies

  • You can use something like this:

    DECLARE @Date DATE = (SELECT DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0))
     
     SELECT DATEADD(M, -1, @Date) AS FirstDay
     SELECT DATEADD(D, -1, @Date) AS LastDay
    

    every day is a school day
    Friday, December 10, 2010 12:21 PM
  • SELECT DATEadd(MONTH,datediff(month,0,getdate())-1,0),DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))
    


    Vishal Gajjar MCITP.DBA \ MCITP.DatabaseDeveloper
    Friday, December 10, 2010 12:37 PM
  • Its Giving error..
    vineesh1701
    Friday, December 10, 2010 12:53 PM
  • which code you are executing, also, please post the error message,
    Vishal Gajjar MCITP.DBA \ MCITP.DatabaseDeveloper
    Friday, December 10, 2010 1:01 PM
  • Its Giving error..
    vineesh1701

     

    DECLARE @Datum DATETIME 
    set @Datum = (SELECT DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0))
     
     SELECT DATEADD(M, -1, @Datum) AS FirstDay
     SELECT DATEADD(D, -1, @Datum) AS LastDay

     

     

     

    Friday, December 10, 2010 1:43 PM
  • hi,

    this might be helpful to you.

    SELECT CONVERT(DATE,DATEADD(D,-datepart(d,getdate())+1,DATEADD(M,-1,GETDATE()))) PreMonFirstDay,
    	  CONVERT(DATE,DATEADD(D,-DATEPART(D,getdate()),GETDATE())) PreMonLastDay

    output:

    PreMonFirstDay PreMonLastDay
    -------------- -------------
    2010-11-01   2010-11-30
    
    (1 row(s) affected)
    


    Thanks & Regards, Pulin
    • Edited by Pulin Zala Friday, December 10, 2010 2:11 PM Convert to only date instead of datetime
    Friday, December 10, 2010 2:03 PM
  • What version of SQL Server are you using?

    This code was written for 2008 / 2008 R2. If you are using a lower version, then change the data type to datetime and do not assign the value in the declaration as Praktikant has suggested.

     

     


    AMB

    Some guidelines for posting questions...

    Friday, December 10, 2010 2:08 PM
  • Here is one method. It uses two reference dates (could be any first/last of two
    consecutive months) to calculate the first and last days of the previous month:
     
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP),
    '20001231') AS last_day,
           DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP),
    '20001201') AS first_day;
     

    Plamen Ratchev
    Friday, December 10, 2010 2:19 PM
  • From SQL2012, there is a new function introduced called EOMONTH. Using this function the first and last day of the month can be easily found.

    select DATEADD(DD,1,EOMONTH(Getdate(),-1)) firstdayofmonth, EOMONTH(Getdate()) lastdayofmonth


    Regards

    • Proposed as answer by SQL-PRO Thursday, April 2, 2015 3:26 PM
    Thursday, April 2, 2015 9:55 AM
  • Like this the best, never knew about the EOMONTH function.

    Just a small change, the person wants the previous month so change the EOMONTH parameter to -2 for the first column and add -1 as a parameter to the EOMONTH function for the second column


    If there was a problem - Yo, I'll solve it

    Thursday, April 2, 2015 3:26 PM
  • All of which has been discussed in many more recent posts on the exact same topic.  So there is no reason to post to this ancient and answered thread.
    Thursday, April 2, 2015 5:01 PM
  • Hello ,

    select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month
    select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, June 8, 2015 12:46 PM
  • Very elegant!  The datatype never changes and it's a simple mathematical calculation since date's are just decimal values.

    Friday, December 11, 2015 2:20 PM
  • DECLARE @PreviousMonthStart DATETIME
    DECLARE @PreviousMonthEnd DATETIME

    SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)
    SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

    PRINT @PreviousMonthStart
    PRINT @PreviousMonthEnd


    SELECT * FROM MyTable
    WHERE MyDate >= @PreviousMonthStart
    AND MyDate  < @PreviousMonthEnd

     
    Wednesday, April 20, 2016 3:09 PM
  • Perfect solution. Well done
    Friday, January 19, 2018 8:15 PM
  • You should use a calendar table.  Rarely do you simply want the first and last day of a month. Your next question is going to be how to I get the first and last business day of a month.

    http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

    Friday, January 19, 2018 8:34 PM