Use of DATETIME and BETWEEN - Part 2

  • Koen Verbeeck (11/18/2011)

    .001 --> .000

    .002 --> .003

    .999 --> .000 (this has the possibility to "jump" to the next day)

    -- This little snippet shows that that is correct. Answer comes back as: 2011-11-19 00:00:00.000


    INSERT INTO #Test VALUES('2011-11-18 023:59:59:999')

    SELECT Sample FROM #Test

    DROP TABLE #Test

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Hi all

    Thanks for the feedback on the question. The results as they stand make for some interesting reading. Only 31% got the answer correct. So this shows that the loss of precision is not as well understood as it could be.

    Out of the incorrect answers, those giving either of the two final options (21% of total) demonstrate an awareness of the rounding issue but not which direction the rounding occurred.

    I'd be interested in the reasoning as to why people chose the first answer. Perhaps it was an unfamiliarity with the syntax or they thought it could have been a trick question.

    That leaves 45% of the sample who were unaware of both.

    For some applications, this may not be a problem. For those where a high precision fot datetime recording is required, then it is something to look out for.

  • Good question, thanks.

  • Good question - surprisingly not widely known in some of the places I've worked. ...until it bit them square in the app! ๐Ÿ˜› ๐Ÿ˜€


  • Very good question. Obvious, but worth noting, I think, that the rounding occurs on the assignment to a datetime variable or column, NOT when doing the SELECT. In other words, after running your sample script, the values you'd see on a flat select (without the WHERE) would NOT be the values you entered, but would already be rounded up or down.

    Rob Schripsema
    Propack, Inc.

  • Good question, Thanks!

    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Great question. Thanks!

  • Good question.

    Koen Verbeeck (11/18/2011)

    cengland0 (11/18/2011)

    Okay, so I knew it rounds the milliseconds to .000, .003, or .007; however, I didn't know when it rounds up or down.

    So, when the time ends in .001, what does it round to?

    same question for .002 and .999 because those are the ones in the QOTD that messed me up.

    .001 --> .000

    .002 --> .003

    .999 --> .000 (this has the possibility to "jump" to the next day)

    for 1,2,4,6,8,9 it's round nearest - the natural rounding method, that you can see in the string

    9->0<-1 2->3<-4 5 6->7<-8

    But (as the formatting makes obvious I hope) that doesn't tell us which way 5 rounds.

    I think 005 always rounds .005 ms to to .007 ms. I'm not sure I like that behaviour, but I think that's what it does.


  • Sweet - good question.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good QOD. This rounding quirkiness is why I've made a habit of avoiding BETWEEN with datetime types. If you have to use BETWEEN, then be aware that the inclusive end-point for a date must end with .997, or .998 if you really want to be crazy, but not .999. That's so completely counter-intuitive that you'd better have a good reason to use that syntax. Rather, I'd suggest you code to specify the start and end of a range with separate conditions.

    declare @startDate datetime

    declare @endDate datetime

    set @startDate = '2011-09-01'

    set @endDate = '2011-10-31'

    Select ID, tranTime from SomeTable

    where TranTime >= @startDate

    and TranTime < @endDate + 1

    -----edit: compare to @startdate with >=, not just > (Did I miss that, or did the SSC formatter mess with it? Dunno, but I had to correct.

  • L' Eomot Inversรฉ (11/18/2011)

    I think 005 always rounds .005 ms to to .007 ms. I'm not sure I like that behaviour, but I think that's what it does.

    I think you're right. Just like, when rounding numerical data, .5 always rounds up.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog:
    SQL Server Execution Plan Reference:

  • Man... that was not an easy one. Took me almost half an hour to figure it out. THANKS!

  • Revenant (11/18/2011)

    Man... that was not an easy one. Took me almost half an hour to figure it out. THANKS!

    I'm glad that you got so much out of it. Thank you. I enjoy answering QOTDs, and feedback such as yours encourages me to submit more


  • Nice question.

  • good question!!!


    [font="Times New Roman"][/font]
    DBA - SQL Server 2008

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

Viewing 15 posts - 16 through 30 (of 38 total)

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