返回最小日期(1753年1月1日)的 SQLServer 函数

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

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

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

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.

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)

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]()

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

What about the following?

declare @dateTimeMin as DATETIME = datefromparts(1753, 1, 1);
select @dateTimeMin;