Sql Server Function to Return Minimum Date (January 1, 1753)

SQL Server function to return minimum date (January 1, 1753)

You could write a User Defined Function that returns the min date value like this:

select cast(-53690 as datetime)

Then use that function in your scripts, and if you ever need to change it, there is only one place to do that.

Alternately, you could use this query if you prefer it for better readability:

select cast('1753-1-1' as datetime)

Example Function

create function dbo.DateTimeMinValue()
returns datetime as
begin
    return (select cast(-53690 as datetime))
end

Usage

select dbo.DateTimeMinValue() as DateTimeMinValue

DateTimeMinValue
-----------------------
1753-01-01 00:00:00.000

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

DateTime.MinValue and SqlDateTime overflow

Basically, don't use DateTime.MinValue to represent a missing value. You can't use DateTime.MinValue in a SQL Server DateTime field, as SQL Server has a minimum value of the start of 1753.

Instead, make your BirthDate property a Nullable<DateTime> (aka DateTime?), and set it to null when you don't have a value. Also make sure your database field is nullable. Then you just need to make sure that that null ends up as a NULL value in the database. Exactly how you do that will depend on your data access, which you haven't told us anything about.

TSQL Default Minimum DateTime

As far as I am aware no function exists to return this, you will have to hard set it.

Attempting to cast from values such as 0 to get a minimum date will default to 01-01-1900.

As suggested previously best left set to NULL (and use ISNULL when reading if you need to), or if you are worried about setting it correctly you could even set a trigger on the table to set your modified date on edits.

If you have your heart set on getting the minimum possible date then:

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT '1753-01-01'
)

C#- Validation for date year less than 1753

All you're currently checking is whether the value can be parsed. If you also want to check something about the value, you have to do that with a normal condition. Fortunately you can do that pretty easily using && (condition). So for example:

args.IsValid = DateTime.TryParseExact(args.Value.Trim(), "MM/dd/yyyy",
    CultureInfo.InvariantCulture, DateTimeStyles.None, out test) &&
    test.Year >= 1753;

(That's if you really need to check that the year is at least 1753 of course. Zohar Peled's comment may well be more useful to you, in terms of suggesting a better field type, or correcting misunderstandings of the type you're using.)

---

Related Topics