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)

About these ads

242 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?

    • 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

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

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

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

  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.

  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

    • 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!!!!!!

  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

  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

  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

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

  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

  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?

  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’

  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

  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.

  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

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

  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?

  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.

  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

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

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

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

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

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

  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.

  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.

  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

  23. No need to use DateDiff. Inside Dateadd we can use another DateAdd .

    Eg. SELECT DATEADD(DD,-1,DATEADD(M, 2,GETDATE()))

  24. how to find the day of month ……..
    only enter month
    ex:month=04
    then
    find the last date of this month…..using vb.net

  25. You’re the man!
    Whenever I have a question google sends me to you…
    And you always have an answer.
    Kudos to you!

  26. @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)

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

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

  29. @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

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

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

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

  32. @jack

    checked code:

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

    works fine.

    Convert date into 103 or 102 format using convert()

  33. This will give the Number of days in current month.
    try it
    select datediff(d,getdate(),dateadd(m,1,getdate()))

        • 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

          • 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

  34. /*
    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

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

  36. 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))

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

  38. 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))

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

    • 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

      • Thans a ton! As you already know, it works (like a charm). I would not have been able to solve this on my own =) Well done!

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

  41. This is the best way to find last day of the month in sql server

    SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())+1,-1)

  42. 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))))

      • 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?

  43. 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?

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

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

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

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

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

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

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

  50. i want date range of one month where any data entered between this month should not duplicate. if data entered after the month should allow to enter.

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

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

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

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

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

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

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

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

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

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

    but can someone explain WHY? Please :)

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

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

  62. 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…

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

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

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

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

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

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

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

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

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

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

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

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

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

    • 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

      • 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

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

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

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

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