Solved

Why 1753?

Posted on 2004-08-19
5
2,032 Views
Last Modified: 2010-05-18
Why does SQL Server's datetime data type and date functions work only back to the year 1753?

PLEASE note: I'm *not* asking what the useable range of years is (1753 to 9999). I'm *not* asking for work-arounds to deal with other years. And I'm *not* asking how the datetime values are stored.

Microsoft hired some of the best brains around to engineer SQL Server from the ground up, and I think it's reasonable to assume they did things for good reasons. I want to know *why* they didn't design the datetime components to work with years before the year 1753.

What's so special about that year?

<various quotes from BOL>
Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999.

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

datetime data type
A SQL Server system data type that stores a combined date and time value from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
</quotes>
0
Comment
Question by:jdlambert1
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11848853
The Gregorian callendar was standarized in 1753.  Prior to that dates were not reliable:  So for example, the same date in Spain did not match the same date in England.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 11848985
To give you a better example closer to home if it was August 19, 1750 in Texas (still part of Spain), in Maryland (still part of Britain) it would not be the same date (they were still using the Julian calendar back then, which was notoriously wrong)

Note: Not all countries changed in 1753 (and in fact one future US state changed later), but most had adopted the Gregorian calendar by then. Microsoft must have considered that was the best date to use.  In fact, they have some documentation to say that they do accept dates prior to 1753 using the JET ODBC driver (yes, I understand that MS Access is not the same as MS SQL Server, but how different can handling dates be?):

<quote>
Microsoft Access allows the storage of date values in the range of 1/1/0000 to 12/31/9999. You can read and write pre-1753 dates by using the Microsoft Access ODBC driver, but the value written or read may not correspond with the actual date stored in the Microsoft Access database. Therefore, reading and writing pre-1753 dates is not supported with the Microsoft Access ODBC Driver unless this hotfix is applied.
</quote>
0
 
LVL 2

Assisted Solution

by:nexusSam
nexusSam earned 100 total points
ID: 11850596
There a 10-day gap in year 1582 to bring the (Julian) calendar more accuracy, forced by Pope Gregor. England, always suspicious of Rome, changed much more slowely. By 1700, which is a leap year on the Julian calendar, the gap between the two calendars was 11 days. Finally, England and it's American colonies adopted the Gregorian calendar by mid-eighteenth century
Astronomers still use the Julian calendar because they need to be able to calculate date back in time and Gregorian calendar has that funky 10-day gap in 1582 ..
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 100 total points
ID: 11854556
I learnt this feature of dates when I began doing genealogy work.
There is no, 100 percent reliable way to get a specific date in our current calendar system for before this period, for the 'world'. It may be close, but it could be off by quite a bit.
In many genealogy packages, they will often write any date in Jan-Mar (the 3 months most affected) before this time as for example

Jan 14, 1750/51

this is annoying when I know for example someone's death date, and their exact age at death, I still cant store a definative year.

If you really need to store dates before this period (and can trust them), then you would have to do a custom app to store seperate fields to contain day/month/year

If you go searching around the web, you can find lots of sites dedicated to when the calanders changed and why and the disagreements about the when and why. If you read enough of them, you will know that very little recorded information from before these dates can be thought of as accurate to the date.
0
 
LVL 15

Author Comment

by:jdlambert1
ID: 11865339
Thanks, guys. Splitting 60/20/20, since Anthony was first in.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now