[sql-server] SQL Server function to return minimum date (January 1, 1753)

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.

It's not January 1, 1753 but select cast('' as datetime) wich reveals: 1900-01-01 00:00:00.000 gives the default value by SQL server. (Looks more uninitialized to me anyway)

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

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

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.

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.

This is what I use to get the minimum date in SQL Server. Please note that it is globalisation friendly:

CREATE FUNCTION [dbo].[DateTimeMinValue]()
RETURNS datetime
AS
BEGIN
  RETURN (SELECT
    CAST('17530101' AS datetime))
END

Call using:

SELECT [dbo].[DateTimeMinValue]()

The range for datetime will not change, as that would break backward compatibility. So you can hard code it.