What is the significance of 1/1/1753 in SQL Server?

by

The decision to use 1st January 1753 (1753-01-01) as the minimum date value for a datetime in SQL Server goes back to its Sybase origins.

The significance of the date itself though can be attributed to this man.

Philip Stanhope, 4th Earl of Chesterfield

Philip Stanhope, 4th Earl of Chesterfield. Who steered the Calendar (New Style) Act 1750 through the British Parliament. This legislated for the adoption of the Gregorian calendar for Britain and its then colonies.

There were some missing days (internet archive link) in the British calendar in 1752 when the adjustment was finally made from the Julian calendar. September 3, 1752 to September 13, 1752 were lost.

Kalen Delaney explained the choice this way

So, with 12 days lost, how can you
compute dates? For example, how can
you compute the number of days between
October 12, 1492, and July 4, 1776? Do
you include those missing 12 days? To
avoid having to solve this problem,
the original Sybase SQL Server
developers decided not to allow dates
before 1753. You can store earlier
dates by using character fields, but
you can’t use any datetime functions
with the earlier dates that you store
in character fields.

The choice of 1753 does seem somewhat anglocentric however as many catholic countries in Europe had been using the calendar for 170 years before the British implementation (originally delayed due to opposition by the church). Conversely many countries did not reform their calendars until much later, 1918 in Russia. Indeed the October Revolution of 1917 started on 7 November under the Gregorian calendar.

Both datetime and the new datetime2 datatype mentioned in Joe’s answer do not attempt to account for these local differences and simply use the Gregorian Calendar.

So with the greater range of datetime2

SELECT CONVERT(VARCHAR, DATEADD(DAY,-5,CAST('1752-09-13' AS DATETIME2)),100)

Returns

Sep  8 1752 12:00AM

One final point with the datetime2 data type is that it uses the proleptic Gregorian calendar projected backwards to well before it was actually invented so is of limited use in dealing with historic dates.

This contrasts with other Software implementations such as the Java Gregorian Calendar class which defaults to following the Julian Calendar for dates until October 4, 1582 then jumping to October 15, 1582 in the new Gregorian calendar. It correctly handles the Julian model of leap year before that date and the Gregorian model after that date. The cutover date may be changed by the caller by calling setGregorianChange().

A fairly entertaining article discussing some more peculiarities with the adoption of the calendar can be found here.

More Related Contents:

  1. Sql Server string to date conversion
  2. How to get Time from DateTime format in SQL?
  3. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  4. Date conversion and culture: Difference between DATE and DATETIME
  5. How can I get the sum of multiple datetime values?
  6. How to calculate difference in hours (decimal) between two dates in SQL Server?
  7. How can I remove duplicate rows?
  8. SQL Server: Examples of PIVOTing String data
  9. Best approach to remove time part of datetime in SQL Server
  10. Check if table exists in SQL Server
  11. Using T-SQL, return nth delimited element from a string
  12. What is the use of GO in SQL Server Management Studio & Transact SQL?
  13. Maximum size of a varchar(max) variable
  14. best way to convert and validate a date string
  15. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  16. Is there a way to simplify a NULL compare of 2 values
  17. TSQL Try / Catch within Transaction or vice versa?
  18. What is the maximum number of characters that nvarchar(MAX) will hold?
  19. TSQL: Create a view that accesses multiple databases
  20. Sequence as default value for a column
  21. Aggregate bitwise-OR in a subquery
  22. How do I flush the PRINT buffer in TSQL?
  23. OR Operator Short-circuit in SQL Server
  24. How to change default language for SQL Server?
  25. T-SQL incrementing counter based on change in a column value
  26. T-SQL XOR Operator
  27. SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]
  28. Random record from a database table (T-SQL)
  29. Random Weighted Choice in T-SQL
  30. time format in SQL Server

Leave a Comment