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

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

  • How can I get the ID of the last INSERTed row using PDO with SQL Server?
  • SQL Server 2012 error: object reference not set to an instance of an object
  • Twp multi select parameters in SSRS not displaying results for when selected more than one value
  • SQL Server 2016 with R Services in the cloud
  • Optimizing CASE WHEN statement in SQL Server WHERE clause condition
  • Selecting from XML field where XML field = X
  • Escape double quotes in SQL 2005/2008
  • The most efficient way to find the top level parent in SQL Server?
  • SQL Server - Trying to de-normalize my table
  • Delete Top-N' Rows from a Table with some sorting(order by 'Column')
  • Insert double quotes into SQL output
  • How to alter db object datatype from varchar to nvarchar without defaulting to null?
  • 5 Solutions collect form web for “What is the significance of 1/1/1753 in SQL Server?”

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

    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.

    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)

    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

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.