What is the significance of 1 1 1753 in SQL Server

Question

Why 1753  What do they have against 1752  My great great great great great great great grandfather would be very offended

User · Answer

Incidentally  Windows no longer knows how to correctly convert UTC to U S  local time for certain dates in March April or October November of past years   UTC-based timestamps from those dates are now somewhat nonsensical   It would be very icky for the OS to simply refuse to handle any timestamps prior to the U S  government s latest set of DST rules  so it simply handles some of them wrong   SQL Server refuses to process dates before 1753 because lots of extra special logic would be required to handle them correctly and it doesn t want to handle them wrong

User · Answer

Your great great great great great great great grandfather should upgrade to SQL Server 2008 and use the DateTime2 data type  which supports dates in the range  0001-01-01 through 9999-12-31

User · Answer

This is whole story how date problem was and how Big DBMSs handled these problems       During the period between 1 A D  and today  the Western world has   actually used two main calendars  the Julian calendar of Julius Caesar   and the Gregorian calendar of Pope Gregory XIII  The two calendars   differ with respect to only one rule  the rule for deciding what a   leap year is  In the Julian calendar  all years divisible by four are   leap years  In the Gregorian calendar  all years divisible by four are   leap years  except that years divisible by 100  but not divisible by   400  are not leap years  Thus  the years 1700  1800  and 1900 are leap   years in the Julian calendar but not in the Gregorian calendar  while   the years 1600 and 2000 are leap years in both calendars       When Pope Gregory XIII introduced his calendar in 1582  he also   directed that the days between October 4  1582  and October 15  1582    should be skipped   that is  he said that the day after October 4 should   be October 15  Many countries delayed changing over  though  England   and her colonies didn t switch from Julian to Gregorian reckoning   until 1752  so for them  the skipped dates were between September 4   and September 14  1752  Other countries switched at other times  but   1582 and 1752 are the relevant dates for the DBMSs that we re   discussing       Thus  two problems arise with date arithmetic when one goes back many   years  The first is  should leap years before the switch be calculated   according to the Julian or the Gregorian rules  The second problem is    when and how should the skipped days be handled       This is how the Big DBMSs handle these questions          Pretend there was no switch  This is what the SQL Standard seems to   require  although the standard document is unclear  It just says that   dates are  constrained by the natural rules for dates using the   Gregorian calendar    whatever  natural rules  are  This is the option   that DB2 chose  When there is a pretence that a single calendar s   rules have always applied even to times when nobody heard of the   calendar  the technical term is that a  proleptic  calendar is in   force  So  for example  we could say that DB2 follows a proleptic   Gregorian calendar     Avoid the problem entirely  Microsoft and Sybase   set their minimum date values at January 1  1753  safely past the time   that America switched calendars  This is defendable  but from time to   time complaints surface that these two DBMSs lack a useful   functionality that the other DBMSs have and that the SQL Standard   requires     Pick 1582  This is what Oracle did  An Oracle user would   find that the date-arithmetic expression October 15 1582 minus October   4 1582 yields a value of 1 day  because October 5   14 don t exist  and   that the date February 29 1300 is valid  because the Julian leap-year   rule applies   Why did Oracle go to extra trouble when the SQL   Standard doesn t seem to require it  The answer is that users might   require it  Historians and astronomers use this hybrid system instead   of a proleptic Gregorian calendar   This is also the default option   that Sun picked when implementing the GregorianCalendar class for   Java   despite the name  GregorianCalendar is a hybrid calendar        Source 1 and 2

User · Answer

1752 was the year of Britain switching from the Julian to the Gregorian calendar   I believe two weeks in September 1752 never happened as a result  which has implications for dates in that general area   An explanation  http   uneasysilence com archive 2007 08 12008   Internet Archive version

User · Answer

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  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

[sql-server] What is the significance of 1/1/1753 in SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to localization

Examples related to internationalization