Menu
Why 1753? What do they have against 1752? My great great great great great great great grandfather would be very offended.
845
If you have worked with SQL databases before, you might have come across the statement WHERE 1=1. It is a common statement that is used to return all the records from a given table. The statement where 1=1 in SQL means true. It is the same operation as running the select statement without the where clause.
The minimum valid date for a SqlDateTime structure is January 1, 1753.
First, create an aggregate query that has two fields: GroupID and RecordDate. Group by the GroupID field, and choose the "Min" option for the RecordDate, to return the earliest date for each query.
There are no built-in values true and false . One alternative is to use strings 'true' and 'false' , but these are strings just like any other string. Often the bit type is used instead of Boolean as it can only have values 1 and 0 . Typically 1 is used for "true" and 0 for "false".
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.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
