Fun(?) with DATETIME2

  • David Data

    SSCrazy

    Points: 2965

    BTW Microsoft's documentation has an error too - it says "Accuracy 100 nanoseconds". This is of course the Resolution (or Precision) of the data.

    The accuracy will depend on the original source of the time-stamp - which in my experience can vary from minutes (old server nobody cares about) to milliseconds (typical NTP setup). You'll only get 100 nSec accuracy in systems very tightly coupled to GPS (or a satellite-coordinated atomic clock). Even then, by the time some code has decided to date an event many many ยตSecs will have passed.

  • Phil Huffstatler

    Old Hand

    Points: 304

    Fired up my local SQL 2008 Mgmt Studio, put the code into the query window (on Master), hit F5, and got: 1752-09-09 00:00:00.0000000

    So, why do I now have an X on my test answer? I wasn't wrong, my SQL install isn't wrong... the world is.... ๐Ÿ™‚

    Oh well,

    Phil

  • SQLRNNR

    SSC Guru

    Points: 281243

    Total guess on this question. The question asks for opinions and the answer is an opinion. Based on that, I see more than one possible answer.

    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

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    I guessed Option:1.

    SSMS QA says: 1752-09-09 00:00:00.0000000

    Not sure, why the correct answer is :: Option No: 3

    Thanks.

  • fourdoorvette

    Grasshopper

    Points: 19

    O.K gentlemen, how does one explain this??

    USE AdventureWorks

    GO

    DECLARE @MYDATE DATETIME2

    SET @MYDATE = '1752-09-09'

    PRINT @MYDATE

    ANSWER

    1752-09-09 00:00:00.0000000

    No Error???

  • Rose Bud

    SSCrazy

    Points: 2971

    vk-kirov (12/27/2010)


    I wonder how many records in one's database containing year 1752 ๐Ÿ˜€

    As a matter of fact, we have several hundred homes in our database that were built during the 1700s!

  • Rose Bud

    SSCrazy

    Points: 2971

    Sourav-657741 (12/27/2010)


    I guessed Option:1.

    SSMS QA says: 1752-09-09 00:00:00.0000000

    Not sure, why the correct answer is :: Option No: 3

    Correct answer is #2.

  • Rose Bud

    SSCrazy

    Points: 2971

    fourdoorvette (12/27/2010)


    O.K gentlemen, how does one explain this??

    USE AdventureWorks

    GO

    DECLARE @MYDATE DATETIME2

    SET @MYDATE = '1752-09-09'

    PRINT @MYDATE

    ANSWER

    1752-09-09 00:00:00.0000000

    No Error???

    I am not a gentleman but I will point out that the author's correct answer is "It runs perfectly but you would expect it to fail." The fact that it runs without error is not up for debate, only what your expectations were.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    wware (12/27/2010)


    I am not a gentleman but I will point out that the author's correct answer is "It runs perfectly but you would expect it to fail." The fact that it runs without error is not up for debate, only what your expectations were.

    How can you debate on one's expectations?

    <Off-topic mode on\>

    And seriously, datetime2? Couldn't Microsoft find a more original name?

    By the way, Microsoft is conducting a survey to choose a name for the "server-less server". Read more about it in the blog post of Aaron Bertrand.

    <Off-topic mode off\>

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

  • UMG Developer

    SSChampion

    Points: 13482

    I agree with the others, that without the mention of Britain in the question, the date is perfectly valid and option 1 should have been the correct answer.

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    UMG Developer (12/28/2010)


    I agree with the others, that without the mention of Britain in the question, the date is perfectly valid and option 1 should have been the correct answer.

    Understand that I'm just playing Devil's Advocate here....

    Microsoft is a U.S. Company. The U.S. was a collection of British colonies back in 1752. Hence, the assumption, on the part of the author, that everyone would be thinking U.S. (or former colonies) time when looking at the answer to this. It's not about Britain so much as it is about everything that was a member of the British Empire. The U.S., as well as Britain, never had a 09/09/1752.

    But I agree that the "you expect" part of the answers could have been phrased in such a way as to not make people think the author was trying to force an opinion upon the readership.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • UMG Developer

    SSChampion

    Points: 13482

    Brandie Tarvin (12/28/2010)


    Microsoft is a U.S. Company. The U.S. was a collection of British colonies back in 1752. Hence, the assumption, on the part of the author, that everyone would be thinking U.S. (or former colonies) time when looking at the answer to this. It's not about Britain so much as it is about everything that was a member of the British Empire. The U.S., as well as Britain, never had a 09/09/1752.

    Microsoft may be a U.S. company, but their products are used world-wide, so types have to account for other countries as well.

    The datetime2 data type is specifically documented as the Gregorian calendar, so unless the question mentions something modifying what is in BOL how are we supposed to know if we use BOL, or what the QOTD author is thinking?. http://msdn.microsoft.com/en-us/library/bb677335.aspx

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Brandie Tarvin (12/28/2010)


    Understand that I'm just playing Devil's Advocate here....

    Microsoft is a U.S. Company.

    Yes, but if I'm not mistaken you can buy localized versions of SQL Server.

    Just playing God's Advocate here ๐Ÿ™‚

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

  • OCTom

    SSChampion

    Points: 11755

    Where's the "fun" in this? :hehe:

    The fact that the given date was not recognized by Britain and her colonies does not mean that the date is not a valid Gregorian date. The correct answer should be "works as expected". I don't know why the author received an error.

    Thanks for the question, though.

  • dave.farmer

    SSCrazy

    Points: 2431

    So let me get this straight... because I knew how the function worked, I am wrong.

    Had I not known how it worked, I would have had a pretty decent chance of being right.

    Very curious question that rewards ignorance whilst penalising knowledge.

    I might submit one along similar lines:

    Q: If I issue a SELECT TOP 5 * FROM CUSTOMER query where the CUSTOMER table has more than 10 rows, how many rows will be returned?

    a) (incorrect) I know it will be 5

    b) (correct) I'm guessing 5 but that's probably wrong

    c) (incorrect) 37

    d) (incorrect) error

    What do you think? Is this the way forward?

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

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