Fun(?) with DATETIME2

Question

Fun(?) with DATETIME2

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

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

David Data SSCrazy Points: 2965 More actions · Answer 1

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 More actions · Answer 2

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: 281334 More actions · Answer 3

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: 33518 More actions · Answer 4

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 More actions · Answer 5

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 Hall of Fame Points: 3147 More actions · Answer 6

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 Hall of Fame Points: 3147 More actions · Answer 7

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 Hall of Fame Points: 3147 More actions · Answer 8

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: 259105 More actions · Answer 9

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 More actions · Answer 10

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: 173045 More actions · Answer 11

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 More actions · Answer 12

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: 259105 More actions · Answer 13

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: 11785 More actions · Answer 14

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 More actions · Answer 15

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?