Go Premium for a chance to win a PS4. Enter to Win


Why 1753?

Posted on 2004-08-19
Medium Priority
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.

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.
Question by:jdlambert1
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.
LVL 75

Accepted Solution

Anthony Perkins earned 1200 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?):

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.

Assisted Solution

nexusSam earned 400 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 ..

Assisted Solution

ChrisFretwell earned 400 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.
LVL 15

Author Comment

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

971 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