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

Question

I am looking for a SQL Server function to return the minimum value for datetime, namely January 1, 1753. I'd rather not hardcode that date value into my script.

Does anything like that exist? (For comparison, in C#, I could just do DateTime.MinValue) Or would I have to write this myself?

I am using Microsoft SQL Server 2008 Express.

Solution 1

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

Solution 2

Have you seen the SqlDateTime object? use SqlDateTime.MinValue to get your minimum date (Jan 1 1753).

Solution 3

As I can not comment on the accepted answer due to insufficeint reputation points my comment comes as a reply.

using the select cast('1753-1-1' as datetime) is due to fail if run on a database with regional settings not accepting a datestring of YYYY-MM-DD format.

Instead use the select cast(-53690 as datetime) or a Convert with specified datetime format.

Solution 4

Enter the date as a native value 'yyyymmdd' to avoid regional issues:

select cast('17530101' as datetime)

Yes, it would be great if TSQL had MinDate() = '00010101', but no such luck.

Solution 5

Here is a fast and highly readable way to get the min date value

Note: This is a Deterministic Function, so to improve performance further we might as well apply WITH SCHEMABINDING to the return value.

Create a function

CREATE FUNCTION MinDate()
RETURNS DATETIME WITH SCHEMABINDING
AS
BEGIN
    RETURN CONVERT(DATETIME, -53690)

END

Call the function

dbo.MinDate()

Example 1

PRINT dbo.MinDate()

Example 2

PRINT 'The minimimum date allowed in an SQL database is ' + CONVERT(VARCHAR(MAX), dbo.MinDate())

Example 3

SELECT * FROM Table WHERE DateValue > dbo.MinDate()

Example 4

SELECT dbo.MinDate() AS MinDate

Example 5

DECLARE @MinDate AS DATETIME = dbo.MinDate()

SELECT @MinDate AS MinDate

View more solutions

Comments

[Joe Stefanelli]

Just curious: Why are you planning to use that date at all as opposed to letting the column be NULL instead?

[Gabe]

You could use CONVERT(smalldatetime, 0) for smalldatetime.

[Gennady Vanin Геннадий Ванин]

CONVERT(smalldatetime, 0) or CONVERT(datetime, 0) or cast(0 as datetime) is not minimum value of datetime

[Jeremy]

@Joe: The column does not allow NULL values, and I don't want to change it since I am not creating this table.

[Jeremy]

This needs to be a SQL function, not a .NET function.

[Jeremy]

I was hoping to avoid writing my own, but it looks like that is my only option.

[Gabe]

If you're going to have an arbitrary constant, '1753-1-1' is much better than -53690

[Jeremy]

@Gabe: Yes, I was thinking the same thing. Coming back to the script, I would have no idea what -53690 means.

[Jeremy]

That is true, but it would still be nice to call GetMinDate() or something like that instead of CONVERT(datetime, '1753-1-1') wherever I needed to use it.

[D'Arcy Rittich]

The function name would make obvious the meaning of the value.

[Andomar]

@Jeremy: I know what convert does, but for GetMinDate() I'd have to dive into the function definition. So if I had to maintain your code, I'd prefer the convert variant.

[Evan]

Although SqlDateTime.MinValue is useful, it's deceptive to have this as an answer. @Jeremy is asking for a native sql function, not something from .NET's class library

[jwg]

You might know what convert does, but you wouldn't necessarily know the significance of 1753-1-1.

[Ram]

There is no function even in SQL Server 2012 to get min date similar to getdate() hence the above one is the only possible solution. This is for anyone who is looking for better solution in sql server 2012 related solution.

[t_plusplus]

plus one for this helped with a similar question but in a .Net context not SQL; as it is the case with this question..

[Kapé]

Maybe you can actually achieve this by using the new U-SQL

[Triynko]

Just use DateTime2(7). It maps to the .NET DateTime type exactly. Using sql 'datetime' is a bad as using 'varchar' instead of 'nvarchar'. 'nvarchar' aligns with .NET String type. Same idea. Then you can just use DateTime.MinValue everywhere.

[Crono]

@Gabe using the -53690 value will make the code agnostic on regional settings though.

[Jim]

As mentioned by Frank Gillich below, cast('17530101' as datetime) will also avoid the regional issues and is slightly more readable than -53690.

[BobHy]

OP wanted to avoid hard-coding a constant in his script. This proposed answer still has a hard-coded constant. It might not have to be embedded in each script, but would have to be embedded in some user-written script.

[BobHy]

@RedFilter - Agreed, this is a stupid incompleteness in SQL Server API, but your answer does not avoid the constraint imposed by OP, simply moves the workaround to another place in his/her script. Consequently, not a totally acceptable answer.

[Cee McSharpface]

just to have this linked, related (max): stackoverflow.com/a/548373/1132334

[bugybunny]

This is not correct. The base datetime is 1 Jan 1900 00:00:00.000 (what you get with SELECT CONVERT(DATETIME, 0)) but the actual minimum value that datetime can hold in SQL Server is actually in 1753 what OP wrote. That‘s when America also switched to the Gregorian calender and SQL Server doesn‘t have to deal with missing days etc. SELECT CONVERT(DATETIME, -53690) gives 1753-01-01 00:00:00.000 and SELECT CONVERT(DATETIME, -53691) gives an error about an overflow.

[ToolmakerSteve]

NOTE: stackoverflow.com/a/39541331/199364 says that cast('17530101' avoids regional issues, so use that instead of cast('1753-1-1'.

[ToolmakerSteve]

I strongly disagree with the objection to having to dive into the function. Its ALWAYS more maintainable to NAME special values. "Magic Numbers" are bad for maintainability. Another downside of hardcoding it everywhere you need it, is the possibility of a typo. Suppose you accidentally type `CONVERT(datetime, '17540101') once. Would be easy to overlook, and might work for most purposes. But someday cause misbehavior by code that is checking for '17530101'. A lurking bug.