SQL Server function to return minimum date (January 1, 1753)
Sql ServerDatetimeFunctionSql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
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 - Sql Server
Have you seen the SqlDateTime object? use SqlDateTime.MinValue
to get your minimum date (Jan 1 1753).
Solution 3 - Sql Server
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 - Sql Server
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 - Sql Server
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
Solution 6 - Sql Server
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)
Solution 7 - Sql Server
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]()
Solution 8 - Sql Server
What about the following?
declare @dateTimeMin as DATETIME = datefromparts(1753, 1, 1);
select @dateTimeMin;
Solution 9 - Sql Server
The range for datetime
will not change, as that would break backward compatibility. So you can hard code it.