DateAdd Function

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

  • 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

  • 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

  • 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

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

  • 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

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

  • That was too easy question.

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

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • answer c

  • easy one +1:-)

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

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

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