DateAdd Function

  • David Burrows

    SSC Guru

    Points: 64734

    David: we are going off topic.

    Quite :ermm:

    I think we all agree that again we have a QoD which is not locale neutral.

    Yep 😉

    My post was not meant to cause offence or consternation in any way :blush:

    Far away is close at hand in the images of elsewhere.
    Anon.

  • webrunner

    SSC-Dedicated

    Points: 30281

    Ninja's_RGR'us (3/4/2009)


    ...

    I'm going to go with:

    b) 2009-02-09 16:35:56.789

    UPDATE: I ran the result and got c) 2009-02-09 16:33:56.790. (My original second choice.) Why do the milliseconds get rounded up?

    Thanks,

    webrunner

    It's the limit of the precision in ms. If the last digit is not a possible value, it gets rounded up or down depending on the closest number.

    Thanks, but could you explain in more detail what happened? I still don't quite understand how the rounding works in this case.

    For example, I ran this:

    select DATEADD(MINUTE,-59,'2009-02-09 12:34:56.785')

    and got this:

    2009-02-09 11:35:56.787

    I know SQL is doing something to get that result, but if anything I thought it would end in .786 in this case, yet it ends in .787.

    Thanks again!

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Chad Crawford

    SSChampion

    Points: 11610

    webrunner,

    .786 isn't a valid millisecond for the datetime datatype...

    All datetimes end in either .xx0, .xx3 or .xx7

    .xx9, .xx0 and .xx1 are rounded up/down to .xx0

    .xx2, .xx3, .xx4 are rounded up/down to .xx3

    .xx5, .xx6, .xx8 are rounded up/down to .xx7

    Check out the BOL "Date and Time" for an example table.

    Thanks,

    Chad

  • webrunner

    SSC-Dedicated

    Points: 30281

    Chad Crawford (3/4/2009)


    webrunner,

    .786 isn't a valid millisecond for the datetime datatype...

    All datetimes end in either .xx0, .xx3 or .xx7

    .xx9, .xx0 and .xx1 are rounded up/down to .xx0

    .xx2, .xx3, .xx4 are rounded up/down to .xx3

    .xx5, .xx6, .xx8 are rounded up/down to .xx7

    Check out the BOL "Date and Time" for an example table.

    Thanks,

    Chad

    Wow, that is odd (well, except for .xx0 :)), but thank you so much for telling me. I never would have figured it out, and there it is spelled out in BOL.

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • notquitexena

    SSCrazy

    Points: 2654

    I thought it was a dead giveaway that only one answer had the correct date prefix (2009-02-09). The wrong answers had 2009-02-10 and 2009-02-08. Except for clicking on the wrong button on mistake, I can't understand why anyone would miss this question.

  • webrunner

    SSC-Dedicated

    Points: 30281

    jpowers (3/4/2009)


    I thought it was a dead giveaway that only one answer had the correct date prefix (2009-02-09). The wrong answers had 2009-02-10 and 2009-02-08. Except for clicking on the wrong button on mistake, I can't understand why anyone would miss this question.

    I got the original question correct, not because of the date but simply by doing the calculations in my head (in this case that was relatively easy for me). I almost second-guessed myself because it seemed uncharacteristic that the obvious QOTD answer was the correct one. Usually there is some trick involved.

    But I got the second question (the one posted in the comments) wrong, because of the way SQL Server rounds milliseconds. Go figure, as they say....

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • john.arnott

    SSChampion

    Points: 11882

    Webrunner (and others following the subthread on my supplemental quiz question),

    The rounding of milliseconds in DATETIME data is to .000, .003, or .007. Keep this in mind when designing queries that test for a time being within a range. An easy error to make would be to try to define a day with the BETWEEN operator. If we want to see if a time value is on the same day as some other time, you may be tempted to strip that target datetime down to just the date and then append a "highest possible" time value.

    With this code, a Time_1 value of '2009-03-05 00:00:00.001' would return true, probably not what you'd want.

    declare @time_1 datetime

    declare @startTime datetime

    declare @endTime datetime

    set @time_1 = '2009-03-05 00:00:00.001'

    set @startTime = '2009-03-04 00:00:00.00'

    set @endTime = '2009-03-04 23:59:59.999'

    Print '@startTime is ' + convert(char(23),@startTime,121)

    Print '@time_1 is ' + convert(char(23),@time_1,121)

    Print '@endTime is ' + convert(char(23),@endTime,121)

    If @time_1 between @startTime and @endTime

    print 'True'

    Play with the values of @time_1 to understand the effect of rounding.

    A better date-range test would be to test for greater than or equal to the start of a date and less than the start of the next date.

    declare @time_1 datetime

    set @time_1 = '2009-03-05 00:00:00.001'

    If @time_1 >= '2009-03-04' and @time_1 < '2009-03-05' -- time portion defaults to zeroes

    print 'True'

    else

    print 'False'

    One final note: SQL 2008 introduces some new data types that allow separation of date and time and also greater precision on time.

  • Marcin Zawadzki

    Mr or Mrs. 500

    Points: 510

    That was too easy question.

  • Jerry Hung

    SSChampion

    Points: 12908

    I thought this was a trick question, but got it right anyway, whew~

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • girish.jawle

    Newbie

    Points: 5

    answer c

  • kapil_kk

    SSC-Insane

    Points: 21316

    easy one +1:-)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 11 posts - 16 through 26 (of 26 total)

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