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

sql-server datetime function
Jeremy picture Jeremy · Sep 29, 2010 · Viewed 269.9k times · Source

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.

Answer

D'Arcy Rittich picture D'Arcy Rittich · Sep 29, 2010

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

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How to convert DateTime to VarChar

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Read More