SQL Server：获取没有幻数的最大/最小可能日期

I know that the SQL Server date datatype is defined to have a range of 0001-01-01 to 9999-12-31. Is there any way to obtain those values using T-SQL without the use of magic numbers (e.g. without something like declare @MaxDate date = '12/31/9999')?

我知道SQL Server日期数据类型的定义范围是0001-01-01到9999-12-31。有没有办法在不使用幻数的情况下使用T-SQL获取这些值(例如没有声明@MaxDate date = '12 / 31/9999')?

3 个解决方案

#1

You definitely can't get the maximum, but I've also just learned that you can't get the minimum either.

你肯定无法获得最大值,但我也刚刚得知你也无法获得最低限度。

You could do this with the datetime type:

您可以使用datetime类型执行此操作:

SELECT CAST(0 AS datetime)
--returns 1900-01-01 00:00:00.000

But you can't do it with the date type:

但你不能用日期类型做到这一点:

SELECT CAST(0 AS date)
--returns Msg 529, Level 16, State 2, Line 1
--Explicit conversion from data type int to date is not allowed.

#2

So this doesn't work without Magic numbers, but if you need functions (I used hex notation as it's a bit more compact and easier to read for numbers this large)

所以如果没有Magic数字,这不起作用,但如果你需要函数(我使用十六进制表示法,因为它更紧凑,更容易读取数字这个大)

SELECT CAST(CAST(CAST(0x2D247f AS BIGINT) AS DATETIME) AS DATE) --, CAST(0x2D247f AS BIGINT)
SELECT CAST(CAST(CAST(0xD1BA AS BIGINT) * -1 AS DATETIME) AS DATE) --, CAST(0xD1BA AS BIGINT) * -1

Although, on the lower end, SQL Server will allow 1/1/1:

虽然在较低端,SQL Server将允许1/1/1:

SELECT CAST('0001-01-01' AS DATE)

But not negative dates (e.g. BC).

但不是负面日期(例如BC)。

#3

There is a sort of round about way of getting. But you could do this:

有一种关于获得的方式。但你可以这样做:

DECLARE @dateHighest nvarchar(400), @dateLowest nvarchar(400)

SET @dateHighest = 'powershell.exe ((([System.Data.SqlTypes.SqlDateTime]::MaxValue).Value).ToString(\""MM/dd/yyyy\""))'

SET @dateLowest = 'powershell.exe ((([System.Data.SqlTypes.SqlDateTime]::MinValue).Value).ToString(\""MM/dd/yyyy\""))'

DECLARE @useTopDate table 
(
    highestDate date
)

DECLARE @useBottomDate table 
(
    lowestDate date
)


INSERT INTO @useBottomDate EXEC xp_cmdshell @dateLowest
INSERT INTO @useTopDate EXEC xp_cmdshell @dateHighest

SELECT TOP 1 * From @useTopDate, @useBottomDate

If you need it in a Stored Proc or function you can use it later.

如果您需要在存储过程或功能中,您可以稍后使用它。

#1