Post reply

Problem with adding a month to February 28

  • August 2, 2004 at 6:34 pm

    #108765

    PRINT DATEADD(m,1,'20050228') yields:

    2005-03-28

    Is this a known bug or am I missing something? Does SQL not know that 2/28/05 is the last day of the month?

    I notice that this happens also for June, where adding a month to 6/30 yields 7/30.

  • August 2, 2004 at 6:47 pm

    #517356

    Actually is doing exactly what you told it to. One month means that if you change the month on the same date and that date exists then it should return that value.

    If your purpose is to navigate the end of months I would recommend you find the FIRST day of the next month and substract 1 day to that

    HTH

     


    * Noel

  • August 2, 2004 at 7:40 pm

    #517357

    Something like this should do the trick:

    declare @date1 datetime

    set @date1 = '20050228'

    select dateadd(day,-day(@date1),dateadd(month,2,@date1))

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 4, 2004 at 12:09 am

    #517552

    Thanks, Phil.

    Carl

  • August 4, 2004 at 12:11 am

    #517553

    Thanks, Noel - that makes sense.

    Carl

  • August 4, 2004 at 6:46 am

    #517610

    Phil, your code is "almost good". Try:

    declare @date1 datetime
set @date1 = '20050731'
select dateadd(day,-day(@date1),dateadd(month,2,@date1))

    You will get 2005-08-30 instead of 2005-08-31.

    Using the same idea the code could be:

    declare @date1 datetime
set @date1 = '20050131'
select dateadd(day,-day(dateadd(month,2,@date1)),dateadd(month,2,@date1))

    Another way would be:

    declare @date1 datetime
set @date1 = getdate()
select DATEADD(month, DATEDIFF(month, 0, @date1) + 2, 0) - 1

    I have to admit that this code is a little bizzare, but it also takes care of the time portion of the date, unlike the previous method.

    Razvan

  • August 6, 2004 at 10:03 am

    #518042

    Thanks, Razvan.

    Carl

  • August 7, 2004 at 10:00 am

    #518100

    Carl, these are similar to some of the others but thought I'd post it anyway

    ------ Finds the LAST day of NEXT month (Time=23:59:59.997) (resolution is 3 ms)

    SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,@Date)+2,0))

        AS LastDayNextMonth

    ------ Finds the LAST day of NEXT month (Time=00:00:00.000)

    SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+2,0)-1

        AS LastDayNextMonth

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 7, 2004 at 11:44 am

    #518102

    Thanks Jeff!

    Carl

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply