Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

722 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