Fun with datetime
-
Hey everyone,
A few of us at work have been wondering about the true nature of the datetime data type within sql server. That is, how does it really store this information? The restriction of Jan 1, 1753 as the earliest date and Dec 31, 9999 as the latest seems to us to be fairly arbitrary - and fairly unneccessary. So, let's look at the makeup of datetime
According to bol, the datetime datatype is made up of two 4-byte integer values. The first of these values is used to represent the number of days before or after Jan 1, 1900. Since a 4-byte integer has 2^32 or roughly 4 billion possible values, this first part of datetime could theoretically measure a range of some 11,767,033.69 years. With such a range at its disposal, why limit the range to a paltry 10,000 years?
The second 4 byte integer of our illustrious datetime type is used to count the number of milliseconds that have elapsed since midnight of the current day. By my math, there are 1000 * 60 * 60 * 24 = 86,400,000 milliseconds in a given day (do I need to account for leap-days?
). Once again, however, the integer value used to hold this data is capable of tracking some 4 billion milliseconds, far more than the 86 million in one real day. Now, it is certainly true that 4 bytes is the smallest whole byte data type that could hold 86 million items and while 4 bytes is a little much to hold the 3.5 million days, it is not too far off from needing 4 bytes. And really, this isn't very important - I am not likely to be processing invoices due on June 5th 345,430 ... But we really want to know.
Why limit the possible values to anything other than the maximum possible?
One answer we have speculated on is with regards to the 9999 year maximum. Perhaps, since most other systems can only be counted on to process 4 digit years it is best that sql server follow suit and limit itself to the same 4 digit year.
We couldn't think of any real reason 1753 was chosen as the earliest year, although one item seems plausible. It was in 1753 that Britain and colonies formalized January 1st as the first of the year, following the adoption of the gregorian calendar. Previously the new year was celebrated sometime in the spring.
Anyone have any thoughts? Know the inside scoop?
Thanks!
Dan B
-
Not sure about the upper limit. What you said makes sense, though I suspect it's more that interchanges of data with other systems may warrant limiting to 4 digit years.
As far as the early date, I heard that this is leftover from Sybase. When Great Britain adopted the Gregorian calendar, it differed from their own by 12 days. Rather than allow potential confusion, the Sybase developers chose this as the lower limit. Rumor, so I can't confirm, but makes sense. (somewhat)
-
I just want to know what I have to do 800 years from now when I'm dealing with my "current date" code.
Signed L. Long
Once you understand the BITs, all the pieces come together
-
The lower date is indeed chosen because of the disjoint in the British calendar when changed to Gregorian. It is interesting that this shift was made at different times in other countries. The upper limit is because the display strings for dates (including ISO) are limited to four digits. Imagine the fun of validaing dates if more than four digits were allowed.
The time portion of datetime actually hold the number of ticks, not milliseconds, since midnight. There are 300 ticks per second (SELECT @@TIMETICKS). This is why the display of milliseconds is always less granular than milliseconds.
You can see the two halves of datetime values by converting them to binary and then chopping that in half:
SELECT CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS int) Days, CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),5,8) AS int) As Ticks
--Jonathan -
Jonathan,
You know, I was wondering why it was accurate to only 1/300 of second if it tracked time in ms...
Part of me wishes there was an extendedDateTime datatype. I feel bad for all those archaeologists out there trying to track the history of fossils and having no way to do so...
-Dan B
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply