Post reply

Use of DATETIME and BETWEEN - Part 2

  • November 18, 2011 at 8:18 am

    #1409947

    Good question, thanks.

  • November 18, 2011 at 9:04 am

    #1410000

    Good question - surprisingly not widely known in some of the places I've worked. ...until it bit them square in the app! 😛 😀

    Cheers

  • November 18, 2011 at 9:33 am

    #1410024

    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.

  • November 18, 2011 at 10:35 am

    #1410067

    Good question, Thanks!

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

  • November 18, 2011 at 10:37 am

    #1410069

    Great question. Thanks!

  • November 18, 2011 at 11:55 am

    #1410116

    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.

    Tom

  • November 18, 2011 at 3:48 pm

    #1410235

    Sweet - good question.

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

  • November 18, 2011 at 5:15 pm

    #1410252

    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.

  • November 18, 2011 at 5:20 pm

    #1410253

    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: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • November 18, 2011 at 10:29 pm

    #1410272

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

  • November 19, 2011 at 12:27 pm

    #1410336

    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

    🙂

  • November 19, 2011 at 4:42 pm

    #1410350

    Nice question.

  • November 21, 2011 at 9:19 am

    #1410822

    good question!!!

    thanks!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

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

  • November 21, 2011 at 1:35 pm

    #1411030

    Great question Dwayne!

    Dwayne Dibley (11/18/2011)

    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.

    I understood the percision, but since the QOTD script was a picture instead of text the last 9 looked like a zero for insert 2.

    Maybe that affected the percision or percentage of correct answers?

    😎

  • November 22, 2011 at 6:07 am

    #1411394

    SanDroid (11/21/2011)

    I understood the percision, but since the QOTD script was a picture instead of text the last 9 looked like a zero for insert 2.

    Interesting.

    That comment surprised me so much that I downloaded the picture, cropped out everything but the 9 at the end of that string, and looked at the bitmap. (Since the image is GIF, there's no lossy compression so the bitmap I see is the one everyone else sees).

    It's very much a clear and obvious 9, not the slightest possibility of taking it for zero unless it is displayed using a ridiculously low pixel size.

    Since I can't imagine you are trying to read text at the size where that would not be a clear 9 without magnification, I think you would have had the same problem with seeing it as a 9 had it been transmitted as text anyway, and your problem with the image might easily have been fixed by application of a screen wipe.

    Tom

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

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