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 (http://blog.SQLAuthority.com)

245 thoughts on “SQL SERVER – Find Last Day of Any Month – Current Previous Next

  1. Hi,
    I see function are cool, but have a bug i think,
    2007-08-31 23:59:59.000
    more usefull i think can be value
    2007-08-31 23:59:59.999
    any idea how to create such time value?

    Like

    • Hi
      thanks Pinal dave for solution

      Mr evisoft
      you can use here

      select dateadd(d,datediff(d,0,dateadd(s,-1,dateadd(m,datediff(m,0,getdate())+1,0))),0)

      try it

      Like

  2. create procedure za1
    —- drop procedure za1
    @var1 as datetime
    as
    select @var1-day(@var1)

    ———– Execution —————
    exec za1 ‘2007-09-30′

    Like

    • ok don’t worry about the problem.
      Copy the below code in your sql:
      —————————————————–

      declare @date datetime
      set @date = ’09/25/2010′
      select DATEADD(DD, -DAY(DATEADD(DD, -DAY(@date),@date))+1,DATEADD(DD, -DAY(@date),@date))

      Like

  3. hi
    it is very nice. But i can’t understand DATEDIFF(m,0,getdate()). It returns 1292. Is it month of starting date what date is supported by sqlserver.

    Like

  4. polina,

    you just have to tweek the script thats posted a little…. checck it out.

    SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) FirstDay_PreviousMonth

    SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) FirstDay_CurrentMonth

    SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) FirstDay_NextMonth

    Like

    • hiee i am new to sql server..
      can u tel me d logic of finding first/last day of the month using these queries???
      I tried a lot but didn’t get it!!!!!!

      Like

  5. >> avsrao,

    The reason why you get 1292 is because it gets you the value of the number of months from 1900 (since sql server start date will be from 1900) to the current date.
    Assuming month as SEPT.
    if you add ( 12*107 + 9 – (first month of 1900)) = 1292

    Like

  6. Sunny,

    Why have you used 2nd DATEADDs in your query of 1st day of month? The 1st one is sufficient.

    SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) FirstDay_PreviousMonth

    SELECT DATEADD(mm, DATEDIFF(m,0,getdate()),0) FirstDay_CurrentMonth

    SELECT DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) FirstDay_NextMonth

    Like

  7. Can you please tell me how to get the first and the last day of the previous month for the following sql in mm/dd/yyyy hh:mm:ss AM format?

    E.g: 09/01/2007 12:47:00 PM and 09/30/2000 5:41:00 PM

    SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) FirstDay_PreviousMonth

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) LastDay_PreviousMonth

    Like

  8. Hi,
    I want to suggest another way to calculate last day of the month:

    DECLARE @Month nvarchar(2)
    DECLARE @Year nvarchar(4)

    SET @Month = ‘2’
    SET @Year = ‘1857’

    Select DateAdd(d, -1, DateAdd(m, 1, @Year+ ‘-‘ + (@Month) + ‘-1 23:59:59.998′))

    Like

  9. i am creating cyrstal reports i want divide month and also concentrate on leap year

    —————————————————————————-
    Period user Time Spent
    —————————————————————————-
    jan 01-jan 15 christopher 40

    jan 01-jan 15 james 67

    jan 16-jan 31 christopher 55

    feb 01-feb 15 james 56

    feb 16-jab 28 james 56

    like that i want report but i dont know how split month

    please tell

    Like

  10. Hi Pinal Dave

    Ur site is very useful for me … i m new to sql server

    recently i have faced 1 interview ,in that they asked some questions which i dont knw

    please provide me the answer for the following

    1 “If one table is locked in front-end , how can we remove the lock from back-end”?

    2 max how many @@ error and raise error we can use?
    3 max how many .ndf files we can create at the time of creation of a database?
    4 how do we know an index is properly used or not?
    5 max how many .ndf,.ldf,.mdf files we can create?

    Like

  11. declare @lastday datetime
    declare @inMonth datetime
    set @inMonth = ‘2/1/2008′
    select @lastday = dateadd(day,-1,dateadd(month,1,@inMonth))
    select convert(datetime,@lastday,102) as ‘LastDay’

    Like

  12. Pinal — what a great site you’ve got! I found my answer here quickly and clearly. I’ll be back often.

    Your syntax gave me exactly what I needed in my query, but I’m not clear on what the s switch/function is in the statment ((DATEADD(s,-1,DATEADD) and I’d like to udnerstand it so I can apply it in the future.

    Thanks!

    Katie

    Like

  13. Dear Murali,

    datediff()- Returns the number of date and time boundaries crossed between two specified dates.

    you have given datediff(mm,0,getdate())
    ‘0’ is nothing in datediff function. so it’s throwing an garbage value.

    You have to give some date instead of 0.
    datediff gives numeric data.

    Like

  14. Hai Pinal

    i have a problem here to get the last day of month..
    let say i have table A that have field month and year only…

    how to select this table based on 2 parameter (month and year) to get last day of that month and year…

    thanks

    Like

      • Now everyone, please, read this. Actually, read it twice! :)

        Never, ever do formatting anywhere but in UI layer. If you follow MVC model, View is the place for that. And why is this?

        I worked for two years in a project where things were formatted in Model, View and in Controller layers. I jumped in in the support phase so all the code was already written. I don’t know if it was ever tested though.

        But. Sometimes culture settings were changed in the ASP.NET server, I really don’t know why or who, so simple parsing from “1,234,567.89” to Decimal became awfully difficult to do because in Finland we use dots as thousand separator, not commas.

        This lead into a situation where user really wanted to enter the value 1234567.89 but ended up with 1.23456789 and vice versa when I displayed values I got from a Web Service.

        So I had three layers where formatting and casting could, and did, go wrong and the program wasn’t the tiniest one. So I spent months in fixing just currency string currency conversions!

        Also the program was written in VB.NET and did not have OPTION STRICT set ON. This lead to some interesting situations with implicit castings from string to int/double and back to string.

        How all this could have been avoided? By keeping the currency values in Decimal data type everywhere in the program and formatting would have been done only when displaying them to the user / reading user input.

        Like

  15. I may be asking a very stupid question however I need to turn the output of your above statement that finds the last day of a specific month into a “YYYY-MM-DD” format with no time and use in a stored procedure query. Trying to do a CONVERT(DATETIME, @variable, 102) doesn’t seem to be doing it. Maybe I’m not thinking clearly but this seems to have worked for everything else. Any ideas?

    Like

  16. Nevermind, I got it. I had to use a CONVERT(CHAR, @variable, 102) instead of DATETIME and it works perfect. Thanks for the information about the last day of each month.

    Like

  17. Hi
    I am new in SQL query writing.
    please help me in writing the query to convert today (system date and time) to EPOCH format seconds.

    I would like to use this query along with my existing helpdesk tool that stores dates in EPOCH (10 digit integer format).

    Regards
    Rajesh Agnihotri

    Like

  18. Can anybody help me how to find first,second,third,fourth last mon,tue,wed,thur,fri,sat,sun of a month(In datetime or date format.).I hav a program scheduled to be run like this,I need to find next run date .
    Thanks……..

    Like

      • SELECT DATEADD(week, DATEDIFF(week,0,dateadd(month,datediff(month,0,’2/28/2010′)+1,0)), 0) This works fine as long as the last day of the month is not a Sunday. I too am looking to find a date fuction to find the Last Monday of the month.

        Like

        • Our developer figured this out. Will work if Sunday is the last month and Leap Year:

          DECLARE @monthOfInterest smallDateTime
          DECLARE @dayOfInterest tinyint

          SET @monthOfInterest = ‘2/29/2012′
          SET @dayOfInterest = 5

          SELECT
          DATEADD(day, -(7 + DATEPART(weekday, ( DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )) – @dayOfInterest)%7,
          DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )

          Like

          • This is great, thanks! Only thing when copying note that:

            …@monthOfInterest)) )) – @dayOfInterest)%7,….

            Should be a Minus sign in there but if you direct-copy it, it is something slightly longer than a minus sign, so just replace.

            Like

    • alter function fn_lastMonday
      (
      @date smalldatetime
      )
      returns smalldatetime
      As
      Begin
      declare @lastdate datetime
      set @lastdate =DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))
      return DATEADD(d,-(DATEPART(weekday,@lastdate)-1),@lastdate)
      End

      For Testing
      declare @date smalldatetime
      set @date=’2010-12-05′
      select dbo.fn_lastMonday(@date),DATENAME(dw,dbo.fn_lastMonday(@date))

      Like

  19. Last Day of Previous month I always used:

    dateadd(dd, -Day(GetDate()), GetDate())

    or (to strip off the time component):

    convert(char(10), dateadd(dd, -Day(GetDate()), GetDate()), 101)

    Is there any reason two calls to GetDate() would be an issue here? I believe they will return the exact same time. If that is the case then this method seems a lot more intuitive, simply go back the number of days you are into the current month. If it is the 5th, go back 5 days which puts you on the last day of the previous month, regardless of how many days that month contains (leap year, 30 vs. 31). GetDate returns a full datetime so it works for any right up to the stroke of midnight.

    Like

  20. Of course, my above comment works only when you only care about the date part. Being in the BI world, I work in dimensions of dates and ranges of time. You can change Dave’s solution to:

    SELECT DATEADD(ms,-2,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    to get all the way up to midnight (“s, -1″ leaves the second before midnight out, and using “ms, -1″ rounds to the next day since datetime is accurate to roughly 3 milliseconds.

    But in those cases I highly recommend using less than the first of the month not between x and this EOM value. Any time that SQL can represent up until midnight will be caught then.

    Select *
    from Orders
    where OrderDate >= @FirstOfLastMonth and OrderDate < @FirstOfThisMonth — not <=

    From a mathematical and linear time aspect, I believe that would be more correct.

    This demonstrates what I think is one of SQL Servers biggest deficiencies; the lack of a simple date data type with no time component.

    Like

  21. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  22. Hi Dave,

    Can someone tell me how to get previous year previous month Date from todays date? I need to know, how we will get 02/29/2008.

    Thanks in advance.

    -Peter

    Like

  23. @Donna

    You can subtract the amount of days in the passed date. That will give the last day of the prior month.

    DECLARE @A DATETIME
    SET @A = ’04/30/2009′
    select DATEADD(d, -DATEPART(d, @A), @A)

    Like

  24. Please help. In my stored procede, I want to assign the return value of ‘SELECT DATEADD(dd, -Day(GetDate()), GetDate())’ to a variable, e.g.

    SET @PreMonthEnd = SELECT DATEADD(dd, -Day(GetDate()), GetDate())

    What is the correct statement I should write? Thanks a lot.

    Like

  25. hi there

    I need to have a bimonthly report i.e. once with 01/01/2009 to 15/01/2009
    and then 16/01/2009 to 31/01/2009

    i want to know how can i get starting and ending dates like 1 and 15 of current month if the current date is > 15 and 16 to 30/31 of the previous month if current date is < 15

    thanks a lot in advance.

    zee

    Like

  26. @zee

    WHERE date-COLUMN >= CASE
    WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
    ELSE DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
    END
    AND date-COLUMN <
    CASE
    WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
    ELSE DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
    END

    Like

    • Actually, I discovered it: dateadd(month, -3, GetDate())
      This will give date and time.
      I am working on formatting to get only the date.
      Thank you.

      Like

  27. How do I pull data from starting day of current year through last day of the previous month(pl. include convert function also).

    thanks a lot.

    Like

  28. @mos: [Edited one statement] Check your code before posting it. And if it works your date don’t show the correct time.

    Like

  29. @jack

    checked code:

    select getdate() – datePart(d,getDate())

    works fine.

    Convert date into 103 or 102 format using convert()

    Like

        • tell me the rule of working day in UK. I have a function which calculate weekoffs as any compny.
          In my compny Sat and Sun are weekoffs. what about yours

          Like

          • Sat n Sun are off Plus in UK there are 8 Bank Holidays.

            You can google it and find out about Bank Holidays in UK.

            Some Holidays are not fixed on date.Examle
            In May there are Bank Holidays,which is first Monday and Last Monday Of May.

            Cheers

            Like

  30. /*
    This function will return total weekends and bank holidays
    */
    CREATE FUNCTION test2
    (@startDate smalldatetime, @endDate smalldatetime)
    RETURNS @weekOffs TABLE (dates smalldatetime,Type char(2))
    AS
    BEGIN

    Declare @day tinyint
    Begin
    SELECT @day = 7- datepart(weekday, @startDate)
    SELECT @startDate = DATEADD(Day, @day, @startDate)
    while @startDate<@endDate
    BEGIN
    INSERT INTO @weekOffs(dates,Type)
    SELECT @startDate,case isBankHoliday(@startDate) when 0 then 'WO' else 'BH' end
    INSERT INTO @weekOffs(dates,Type)
    SELECT DATEADD(Day, 1, @startDate),case isBankHoliday(DATEADD(Day, 1, @startDate)) when 0 then 'WO' else 'BH' end
    SELECT @startDate = DATEADD(Day, 7, @startDate)
    END
    RETURN
    END

    CREATE function isBankHoliday(@date smalldatetime)
    returns bit
    as
    Begin
    declare @result bit
    if Exists(select * from bankholidayTable where CONVERT(varchar,bankHolidayDate,105)=CONVERT(varchar,@date,105))
    set @result=1
    else
    set @result=0
    return @result
    End

    Like

  31. i want to calculate the number of day in a previous months.

    because i want to calculate the no.of workig day in a previous month

    suggest me

    Like

  32. To get the date and time as midnight without having to do a cast convert etc,
    i.e. ‘2010-05-31 00:00:00.000′

    Just change the seconds to -86400 rather than -1 which is the number of seconds in a day.

    E.g.

    DECLARE @Date DATETIME
    SET @Date = DATEADD(s,-86400,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    Like

  33. Hi all,

    how to get start date and end date from the following duration as ‘Jan 10 – Jun 10’?

    Can you tell any suggestion as soon as,

    thanks to all

    Like

  34. This may sound like a strange question but how do you find the first day of month say 3 months before Last Day of Previous Month(as defined by “SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) “)
    So my script looks something like this

    and startdte >= “”Here is where I needthe first day of the month three months prior to the last day of the previous month””
    and startdte <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    Like

  35. I need to amend the query to take weekends into consideration – more precisely, I would like to get the last day in a month that is not a weekend. Is it possible to write such an sql query?

    Cheers.

    Like

    • Try this logic

      select last_date+case when datepart(weekday,last_date) =1 then -2 when datepart(weekday,last_date) =7 then -1 else 0 end from
      (
      select dateadd(month,datediff(month,0,’20090120′)+1,-1) as last_date
      ) as t

      Like

  36. Please help me with this, i have a table with the follwoing fields:

    Date
    Duration (in Months, eg 6 months, 12 month or 18 months)
    StartDate
    EndDate

    from StartDate, I want to find out last date of the month as EndDate, which is determined by Duration(in Months).

    Regards.

    Like

  37. i have the following code to get the records from today to two months in the past (from 1st day of that month), how can i add condtion to get the months from previous year e.g. december 2010 when i run the query in 2011

    dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))

    Like

      • thans Madhivanan,

        I added the suggested line of code and run the query now I’m not getting any records.
        dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))
        and
        dt_updated > (select dateadd(year,datediff(year,0,getdate())+1,0)-1)
        perhaps I need to combine the two into one. any hint?

        Like

  38. I am using the first of previous month and last of previous month….
    WHERE prof.profdate between DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    I keep getting some results with dates for 12/01/2010…I am only looking for the dates between 11/01/2010 and 11/30/2010…

    The prof.profdate column is smalldatetime…Does anyone see what I am doing wrong?

    Like

  39. ok, so I am stuck and you all seem to know what you are taling about! (and you are going to think this query is really simple!)

    I have this

    (dateadd(d,-1,getdate()))

    and I have added it into the SQL function in Excel. When it is refreshed it changes the data to the time that it currently is.

    I want this to show data from the previous day midnight to midnight.

    Can anyone explain in plain english how I do this please?

    sarah

    Like

  40. last date of current month
    select DATEADD(m,1,GETDATE() -DAY(getdate()))
    last date of next month
    select DATEADD(m,2,GETDATE() -DAY(getdate()))
    last date of previous month
    select DATEADD(m,0,GETDATE() -DAY(getdate()))

    I am sorry if someone has already suggested it

    Like

  41. You can get last days of all months with the below code:

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+number-1,0))from master..spt_values where type=’P’
    and number>=0 and number<12

    Like

  42. Dear all,
    should the following code bring the Last day of Previous Month? thanks!

    DATEADD(s, -86400, DATEADD(mm, DATEDIFF(m, 0, GETDATE()),0)))

    Like

  43. Thanks for your reply.
    Your query would only return the record with latest date but not all records.
    In my case there are different products and the product quantities are not updated on daily basis.
    For eg.
    I have 3 products ..
    Updateddatetime Productname qtyupdated
    2011-01-28 10:25:47.253 p1 10
    2011-01-29 12:25:47.253 p1 20
    2011-02-20 10:25:47.253 p2 10
    2011-02-22 10:25:47.253 p2 90
    2011-02-20 10:25:47.253 p3 50
    2011-02-22 11:25:47.253 p3 60

    I should get the result as
    Updateddatetime Productname qtyupdated

    2011-01-29 12:25:47.253 p1 20
    2011-02-22 10:25:47.253 p2 90
    2011-02-22 11:25:47.253 p3 60

    that is latest of each record.
    Please help

    Like

  44. No,
    This would not give the desired result.
    max(Updateddatetime) will always give the latest date not all latest records.

    Thanks for your reply.
    Your query would only return the record with latest date but not all records.
    In my case there are different products and the product quantities are not updated on daily basis.
    For eg.
    I have 3 products ..
    Updateddatetime Productname qtyupdated
    2011-01-28 10:25:47.253 p1 10
    2011-01-29 12:25:47.253 p1 20
    2011-02-20 10:25:47.253 p2 10
    2011-02-22 10:25:47.253 p2 90
    2011-02-20 10:25:47.253 p3 50
    2011-02-22 11:25:47.253 p3 60

    I should get the result as
    Updateddatetime Productname qtyupdated

    2011-01-29 12:25:47.253 p1 20
    2011-02-22 10:25:47.253 p2 90
    2011-02-22 11:25:47.253 p3 60

    that is latest of each record.
    Please help

    Like

  45. Q1) Given Month, Year and Day.
    List out all the “Monday” dates of current month and year

    Q2) Suppose loan has been taken on 1-Jan-2001 and after exactly One Month the Due will come for Installment payment (i.e. on 1-Feb-2001)
    So find out the Due date of the Installment if on any random date loan is given

    Like

  46. IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
    END

    –UNION ALL

    IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16’)
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
    AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
    END

    hi all, i need to make union this two if condition Queries with groupby condition,
    pls help…… Thanks in advance

    Like

  47. Arthur, You are an [removed word]. Your method of calculating the last day of the month does not work. You should test your code before you post it for the world to see. I have wasted so much time attempting to implement your solution. Thanks

    Like

    • Steve: Arthur’s code does work, you just need to replace the single quotes with real single quotes. Unfortunately the html reply box on this page alters single quotes to this symbol ‘

      Still, it’s not elegant as you have to supply both the year and month, and the datatype returned is a string so overall not very useful.

      Like

  48. hi ,
    I m new to sql server.

    I have question like i want to display records of last 30 days from currentDate with additional column say DayNo,monthNo,yearNo which contains only day,month,year…

    Can anybody help me out…

    Thanks .

    Like

  49. Sir I want to calculate previous months records from my table .
    like now is month january ,then i want to calculate dec 2011
    count records .

    Please help me

    Like

  50. Hi Pinal,

    I want a function to calculate the last day of all the months based on Year number and Month number but not based on GetDate() that gives the current Date.

    Please help me out.

    Thanks in Advance,
    Samyuktha

    Like

  51. Write a function to return total experience in ,months/years according to the given input(M/Y). Use this in a SP to get the details of a person with his name, experience.

    Like

  52. how can i find out the last day of previous year using sql..
    for example today is 03rd march 2012 , i have to get a result in the following format ‘2011-12-31′ Plz help me… thanks in advance

    Like

  53. This works perfectly
    CONVERT(VARCHAR(8),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),1)

    but can someone explain WHY? Please :)

    Like

  54. Hey Guys:
    Would love some help on this. There is an “Accounting_Date” column in my table and I would like to create a query to reference this date returning the last day of the month. For example if the date column is 2010-01-13 00:00:00.000, I would like it to return 01/31/2010. Is there anyway to make that happen? Thanks in advance.

    Like

  55. For those who want date only (ie without the time)use this

    SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS LastDayOfPrevMonth

    Like

  56. Hello. Sir.. I want to get records like..
    Month Count
    Jan 012 10
    Feb 22
    march 2

    from current month to last all months, current month is May and i want to get Jan, Feb,March, Apr, and May related records… Please help me…

    Like

  57. How about to find last month (max) but the date is appear all.
    For example in the database there are:
    (year-month-day)
    2012-04-01
    2012-04-02
    2012-04-03
    2012-05-01
    2012-05-02
    2012-05-03

    In this case, I just want 2012-05-01, 2012-05-02, and 2012-05-03 would appear (current month).
    What is in query? Cause I tried >
    SELECT CURRENTMONTH FROM DATE WHERE CURRENTMONTH = (SELECT MAX(CURRENTMONTH) FROM DATE)
    Query result is only show the last month and last day > 2012-05-03.

    Thanks.

    Like

  58. While these queries do work, when I tried to use them within a CONVERT that converted the result to a varchar and used it as part of a where clause, it took over two minutes to execute against my database. I looked around and found another way of getting the last day of the month, as seen in this post http://vadivel.blogspot.com/2012/03/find-last-day-of-month.html, put that within the CONVERT and the query took less than a second to run. You may want to look at the other post if you are trying to get the last day of the month since it offers three different ways of getting the same result. Just more alternatives to consider.

    Like

  59. Hi,
    Wondering how I can get 22nd Date of Previous month. eg if today is 25/06/2012 then last month 22nd Date should be 22/05/2012.
    Thanks

    Like

  60. How do I find the last 12 months from a given date including the current month. For example:
    If the given date is 15-Jan-2012
    It should return; jan 2012, dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011

    or if the given date is 31-Dec-2011
    It should return; dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011, jan 2011

    Like

  61. I use this code to return the last friday in any month:

    SELECT DATEADD(day, (DATEDIFF (day, ‘2010-01-01′, (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))) / 7) * 7, ‘2010-01-01′) AS “LAST_FRIDAY_OF_MONTH”

    Like

  62. Hi,

    I am trying yo replicate the ‘n’th week and nth day of that week from the defined calender table. can anyone help me in writing query for the same, thanks..

    Velmurugan

    Like

  63. DECLARE @MyDate datetime
    SET @MyDate=’15/03/1977′
    DECLARE @Interval int
    SET @Interval=0

    SELECT DATEADD(day,-day(@MyDate),DATEADD(mm, 1+@Interval,@MyDate))

    Like

  64. 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

    Like

  65. 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.

    Like

  66. –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))

    Like

    • –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))

      Like

  67. 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

    Like

    • 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

      Like

      • 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

        Like

  68. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  69. 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];

    Like

  70. 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.

    Like

  71. 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

    Like

  72. 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]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s