SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun(?) with DATETIME2


Fun(?) with DATETIME2

Author
Message
vikram.gharge
vikram.gharge
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 10
Plz Tell me new things in sql server 2008 , I want to know it and want to use this sql server for my next project.
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
vikram.gharge (3/29/2011)
SQL Server 2008 brought us some new date types including DATETIME2 (which appears better in every way than DATETIME). But what happens if you run the following script (it will only work in SQL Server 2008 or later)?

DECLARE @myDate DATETIME2SET @myDate = '1752-09-09'PRINT @myDate
By Richard Warr

Answer: It runs perfectly but you would expect it to fail.

Explanation: The DATETIME2 type removes the "pre-1753" restriction of DATETIME. But it is unable to cater for the fact that there was no such date as 9th September 1752. Were it perfect we'd see:

"Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string."
Because of the move to the Gregorian Calendar, Britain and its possessions (including the USA - this WAS 1752) lost 11 days in September so there was nothing between the 2nd and the 13th.
There's some fun to be had with 1751 as well but we'll leave that for another holiday.



This is just nonsense - the DATETIME2 datatype represents dates in the Gregorian calendar, not dates that have been used in the national calendar of Great Britain and its colonies; the date 9th September 1752 was not used in that national calendar, but there certainly is such a date in the Gregorian calendar. So DATETIME2 is already perfect, and preventing it from representing that date would render it imperfect, not improve it.

Tom

john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3059
vikram.gharge (3/29/2011)
SQL Server 2008 brought us some new date types including DATETIME2 .............There's some fun to be had with 1751 as well but we'll leave that for another holiday.


Vikram,

What's your point in copy/pasting the QOD? Did you mean to comment and forgot?
rgtft
rgtft
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 Visits: 754
Poor question; how do you know what my expectations were?



hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2867 Visits: 608
I expected it to work and it did ( as it should in most countries except in brittain ) Sad

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
hakan.winther (8/10/2011)
I expected it to work and it did ( as it should in most countries except in brittain ) Sad

Not in most countries except Britain (note single t), in all countries including Britain. Datetime2 uses the Gregorian calendar, not any other calendar, so it is not a locale dependent type.

Tom

Revenant
Revenant
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7479 Visits: 4865
Tom.Thomson (8/10/2011)
. . .
Not in most countries except Britain (note single t), in all countries including Britain. Datetime2 uses the Gregorian calendar, not any other calendar, so it is not a locale dependent type.

Hmm... maybe it is not quite Gregorian. If you run this, --

DECLARE @type2 DATETIME2(7) = '1582-10-10';
PRINT DATEPART( dw, @type2 );



you will get 1, i.e., Monday, but that is wrong because there was no October 10 in 1582.
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14672 Visits: 12238
Revenant (8/10/2011)
Tom.Thomson (8/10/2011)
. . .
Not in most countries except Britain (note single t), in all countries including Britain. Datetime2 uses the Gregorian calendar, not any other calendar, so it is not a locale dependent type.

Hmm... maybe it is not quite Gregorian. If you run this, --

DECLARE @type2 DATETIME2(7) = '1582-10-10';
PRINT DATEPART( dw, @type2 );



you will get 1, i.e., Monday, but that is wrong because there was no October 10 in 1582.


Actually if you have Monday as day 1 you will get 7, because that date is a Sunday in the Gregorian calendar. I suspect you have the US default (datefirst = 7, so dw=1 on Sunday), since you presumably got 1.

I guess you and I mean different things by "The Gregorian Calendar"; if "Gregorian Calendar" means the national calendar of the United Kingdom of Poland and Lithuania, or some of the Italian states, or Spain, or Portugal that date didn't exist (in the national calendars of everywhere else in the Western world it did). If you mean the calendar using the 365.2525 day year in common use throughout the western world, in which today is 10 August 2011, extended backwards and forwards according to its regular rules without limit (which is the most common meaning, I believe) 10 Oct 1582 certainly does exist in that calendar.

Using calendars of particular nations can lead to strange results. For example in England the months of January and February did not legally exist in 1751, but they did exist in Scotland (Scottish Jan 1751 was English Jan 1750; almost everyone in England already used the Scottish dating for everything except legal documents and taxation; it was just the legal system in England that was out of step). Scotland had begun the year on 1 Jan since 1600, while up to and including 1751 England began the year on 25th March. That's where we get our 6 April to 5 April taxation year from - it was 5 April to 4 April from 1752 to 1799, then from 6 April to 5 April from 1800 to 1899 in order to retain the same Julian date; but it wasn't adjusted in 1900, which was the next year when an adjustment would have been needed to maintain the Julian connection (whether because the 1751 act was interpreted differently or because the powers that be had forgotten that that act had been interpreted in 1800 as demanding such an adjustment).

Tom

john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3059
That's a great post, Tom, showing examples of the variety in calendar usage. It's truly fascinating in a way that appeals to my inner "geek". It also provides a great sample of reasons to consider DATETIME2 simply a data type and nothing more. It holds values of dates following certain construction rules (number of days in a month, which is a leap year....), but does not attempt to codify the varieties of calendar adjustments made in different locales over the centuries. I think that's what you were saying too, in the last sentence of the first paragraph.

I think that's a fair thing for MS to have done. If I'm storing dates of historical events in the eighteenth century, then I ought to make any duration calculations aware of the Old Style/New Style break in 1752, or any other anomalies like the Scottish/English calendar difference.
Revenant
Revenant
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7479 Visits: 4865
I logged that October 5-14, 1582, unaccounted-for gap as a bug against Windows. (SQL Server gets the weekday via a WinAPI call.)

I was told that this will be “soon” corrected for the cultures (languages) that were on the Roman calendar at that time. Win guys are not exactly sure what to do about countries that converted later; most notably Russian Orthodoxy, which converted only after the October Revolution of 1917. (What do you do if the date and time are marked as GMT but the culture is one of the Eastern Orthodoxies? Any suggestions for precedence of calculation?)

This goes all the way back to The Bard and his oft-quoted “time is out of joint,” which alluded to the fact that when he wrote Hamlet, the English calendar was out of sync with the Roman one.

I guess this means that someone will have to think through comparisons of dates on servers that are set to different cultures. (The mentioned October Revolution, which was logged on October 25, 1917, is now celebrated on November 7.)

Never a dull moment.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search