Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The decision to use 1st January 1753 (<code>1753-01-01</code>) as the minimum date value for a datetime in SQL Server goes back to its <a href="https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise#History" rel="noreferrer">Sybase origins</a>.</p> <p>The significance of the date itself though can be attributed to this man.</p> <p><img src="https://i.stack.imgur.com/EGnwX.png" alt="Philip Stanhope, 4th Earl of Chesterfield"></p> <p>Philip Stanhope, 4th Earl of Chesterfield. Who steered the <a href="http://en.wikipedia.org/wiki/Calendar_%28New_Style%29_Act_1750" rel="noreferrer">Calendar (New Style) Act 1750</a> through the British Parliament. This legislated for the adoption of the Gregorian calendar for Britain and its then colonies. </p> <p>There were some <a href="http://i.justrealized.com/2010/04/15/why-some-dates-are-missing-in-year-1752/" rel="noreferrer">missing days</a> 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.</p> <p>Kalen Delaney <a href="http://www.sqlmag.com/article/sql-server-2000/inside-datetime-data.aspx" rel="noreferrer">explained</a> the choice this way</p> <blockquote> <p>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.</p> </blockquote> <p>The choice of 1753 does seem somewhat anglocentric however as <a href="http://www.sizes.com/time/cal_Gregadoption.htm" rel="noreferrer">many catholic countries</a> in Europe had been using the calendar for 170 years before the British implementation (originally delayed due to opposition <a href="http://www.polysyllabic.com/?q=calhistory/gregorian" rel="noreferrer">by the church</a>). 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.</p> <p>Both <code>datetime</code> and the new <code>datetime2</code> datatype mentioned in <a href="https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server/3310627#3310627">Joe's answer</a> do not attempt to account for these local differences and simply use the Gregorian Calendar.</p> <p>So with the greater range of <code>datetime2</code></p> <pre><code>SELECT CONVERT(VARCHAR, DATEADD(DAY,-5,CAST('1752-09-13' AS DATETIME2)),100) </code></pre> <p>Returns</p> <pre><code>Sep 8 1752 12:00AM </code></pre> <p>One final point with the <code>datetime2</code> data type is that it uses the <a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar" rel="noreferrer">proleptic Gregorian calendar</a> projected backwards to well before it was actually invented so is of limited use in dealing with historic dates.</p> <p>This contrasts with other Software implementations such as the Java <a href="http://download.oracle.com/docs/cd/E17476_01/javase/1.5.0/docs/api/java/util/GregorianCalendar.html" rel="noreferrer">Gregorian Calendar</a> 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 <code>setGregorianChange()</code>. </p> <p>A fairly entertaining article discussing some more peculiarities with the adoption of the calendar <a href="https://gist.github.com/coffeemug/6168031" rel="noreferrer">can be found here</a>.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the significance of 1/1/1753 in SQL Server?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USMartin Smith
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat is the significance of 1/1/1753 in SQL Server?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for great portrait of non-offended great great great great great great great grandfather. Also other shining attributes of this answer.
      singulars
      1. PO
      1. USSmandoli
    2. CO+1 for an answer that is both technical and historical. On a board frequented by heavy left-brainers, this is an enjoyable read. And yes, I did go to a liberal arts college.
      singulars
      1. PO
      1. USMatt Hamsmith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload