Fun(?) with DATETIME2

Question

Fun(?) with DATETIME2

Viewing 15 posts - 31 through 45 (of 81 total)

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

perrone.ci Grasshopper Points: 11 More actions · Answer 1

Good morming!

I disagree with this answer, because SQL Server 2008 supports a data time like 1752 in datetime2 .I executed the code in my SQL Server 2008 and runs normally, so I don't understand this explanation.

DBA_Dom SSCrazy Points: 2902 More actions · Answer 2

Tricky, tricky, trick question. This would take a history buff to get this one right. Thanks for the History leason.

jts2013 Hall of Fame Points: 3226 More actions · Answer 3

I haven't noticed datetime2 before so thanks to the author for bringing this to my attention. I can see it would be useful in some scenarios.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

I'm a fairly old DBA but even I had forgotten that we lost these days back in 1752.

Anthony McBroom Valued Member Points: 67 More actions · Answer 5

I am going to side with many of the other professionals here and say that I expected it to work with Denali and it did! So I would like a full refund on my point deduction for failing miserably at this question.:hehe:

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 6

Cliff Jones (12/30/2010)
I'm a fairly old DBA but even I had forgotten that we lost these days back in 1752.

You dinosaur, you!

@=)

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Brandie Tarvin (12/30/2010)
Cliff Jones (12/30/2010)
I'm a fairly old DBA but even I had forgotten that we lost these days back in 1752.
You dinosaur, you!
@=)

Absolutely, thanks for pointing that out!

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 8

Iulian -207023

SSCertifiable

Points: 7639

January 3, 2011 at 12:29 pm

#1268636

Nice one thanks

Iulian

john.arnott SSChampion Points: 11882 More actions · Answer 9

Now that we can store dates earlier than 1753, there should be no error storing them, but it seems that it will be up to an application using those dates to keep track of invalid dates in September 1752 (or else-time for other locations) when using DATEDIFF() or DATEADD().

For instance, if you know that Thomas Jefferson was born April 2, 1743 and died July 4, 1826 and you want to find his age in days at death, you'd have to subtract the nine missing September 1752 dates from your DATEDIFF() result.

Toreador SSChampion Points: 11365 More actions · Answer 10

David Data (12/27/2010)
I wonder if anyone has created a nation-sensitive historic date system in which [font="Courier New"]Date('1752-09-02') + 1[/font] would give the correct result depending on country. e.g. [font="Courier New"]'1752-09-13'[/font] in England (and I think the rest of the UK), and [font="Courier New"]'1752-09-03'[/font] in The Netherlands? It would be hard to do; you couldn't use ISO country codes to indicate locale, as some no longer exist and others (e.g. Belgium) were parts of multiple other countries at the time.

It wouldn't just be hard to do, it would be both impossible and undesirable to do in a generic automated way (ie not done specifically for an individual application).

I'm in England, so that date did not exist. But my database contains details of historic events in other countries, so needs to be able to store that date. Therefore it is correct (and expected) behaviour that it can do this successfully.

If I wanted to prevent it, I would need to write code myself, based on the value of a Country column associated with the date.

jobpynadath Grasshopper Points: 16 More actions · Answer 11

when we run

DECLARE @myDate DATETIME2

SET @myDate = '1752-09-09'

PRINT @myDate

in sqlserver2008

we get following message

1752-09-09 00:00:00.0000000

then how to become wrong

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 12

Can't quite understand why I should expect it to fail unless I had never understood DateTime2 in the first place. I expected it to work and it did.

Sure is nice not to have to use Julian date functions on date now.

Jamie

salimdkg4 SSC Enthusiast Points: 129 More actions · Answer 13

I have run this query o sql2008 server and output is perfect

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 14

I selected the first option (“It runs perfectly as you would expect”). The correct answer was – “It runs perfectly but you would expect it to fail”. Apparently I knew what SQL Server would do, but I didn’t know what I should expect:-)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 15

Adi,

I too am ait put off by that assumption. Maybe the question should be tossed.

J

Jamie