DateTime Precision

  • Sunil Chandurkar (11/28/2011)


    I overlooked that it is a STRING comparison and got the answer wrong.:-) (though writer had intention to show datetime comparison)

    I updated the code as follows and executed:

    DECLARE @DATE DATETIME

    SET @DATE = '2011-07-31 00:00:00.001'

    IF (@DATE BETWEEN '2011-07-01' and '2011-07-31')

    PRINT 'Yes'

    ELSE

    PRINT 'No'

    The output came 'Yes'. However when I changed the @date value to '2011-07-31 00:00:00.002' the result was 'No'. As per my knowledge the @date value should be compared to the end date '2011-07-31 00:00:00.000'. How '2011-07-31 00:00:00.001' be less than or equal to '2011-07-31 00:00:00.000'?:unsure:

    I executed this on SQL Sever 2008 EE Ver. 10.0.4064.0

    This is because .001 is rounded to .000, giving you the Yes result.

    .002 is rounded to .003, giving you the No result. The datatime datatype is only accurate to every 3 milliseconds. This was in a QoTD a few days ago 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Disgraceful trick question - it's not about the topic it claims to be about.

    However, the utterly wrong explanation suggests that maybe the author didn't even realise that, so maybe the use of "Datetime" in the topic was not an intentional deception.

    Tom

  • Koen Verbeeck (11/27/2011)


    Silly trick question...

    It would be a silly trick question if the author intended it to trick us.

    The "explanation" suggests that the question author fell for the trap himself. He (or she?) simply didn't realize that string comparison would be used, then went on to think up a different explanation - one that obviously contradicts BOL. :laugh:

    It would have been a nice (not silly!) and great trick question if the explanation had read: "SQL Server will use string comparison, d'oh!"


    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/

  • So what is the correct query in your origninal question to correctly compare the datetime field with the 2 date fields? In other words how does one truncate the time portion from the datetime field to do the comparison?

    Bruce

  • Hugo Kornelis (11/28/2011)


    Koen Verbeeck (11/27/2011)


    Silly trick question...

    It would be a silly trick question if the author intended it to trick us.

    The "explanation" suggests that the question author fell for the trap himself. He (or she?) simply didn't realize that string comparison would be used, then went on to think up a different explanation - one that obviously contradicts BOL. :laugh:

    It would have been a nice (not silly!) and great trick question if the explanation had read: "SQL Server will use string comparison, d'oh!"

    I've realized that by now. I wasn't really awake this morning when I wrote my reply. 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Bruce.sexton (11/28/2011)


    So what is the correct query in your origninal question to correctly compare the datetime field with the 2 date fields? In other words how does one truncate the time portion from the datetime field to do the comparison?

    Bruce

    Check out the previous comments, they have all the explanation you need.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Oops - I got it wrong:blush:

  • Good question. I have ran into issues with BETWEEN on several occasions.

    http://brittcluff.blogspot.com/

  • declare @date datetime

    Set @date = '2011-07-31 00:00:00.000'

    IF (@date BETWEEN '2011-07-01' and '2011-07-31')

    PRINT 'Yes'

    ELSE

    PRINT 'No'

    does return Yes

  • vk-kirov (11/27/2011)


    Nice tricky question with stupid 'explanation'.

    Of course, the string (not datetime) '2011-07-31 00:00:00.000' is greater than the other string '2011-07-31', and thus the result of the batch is 'No'.

    Thanks for this. The question and the answer bothered me, but once I realized I was doing a CAST/CONVERT in my head (that wasn't there) everything made sense.:-)

  • You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.

    Would it be so hard to introduce a new quote character for dates, ala Access?

    For example:

    #02/21/2008# or #12/31/1899 14:23#

    Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...

  • roger.plowman (11/28/2011)


    You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.

    Would it be so hard to introduce a new quote character for dates, ala Access?

    For example:

    #02/21/2008# or #12/31/1899 14:23#

    Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...

    I write SQL for both MSSQL servers and Access. I get those dates mixed up all the time when I'm programming for Access. I keep forgetting to put those #'s instead of single quotes. I do wish they were the same.

  • roger.plowman (11/28/2011)


    You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.

    Would it be so hard to introduce a new quote character for dates, ala Access?

    For example:

    #02/21/2008# or #12/31/1899 14:23#

    Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...

    That's a good suggestion. It would get rid of some nasty accidental implicit conversion bugs.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • roger.plowman (11/28/2011)


    Using single quotes for both strings and dates is a brain-dead design decision--period.

    Technically, T-SQL is not using single quotes for both strings and dates. It uses single quotes for strings only.

    There is no way to have date literals in T-SQL; that's why we have to make do with string literals that will be converted (at execution time) to date or datetime values.

    </pedantic>


    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/

  • My apologies. I have corrected the explanation to note the string comparison.

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

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