在ms sql server 2005+中找到最大可能的日期。

Is there a function like GETDATE() in Sql Server 2005 that let's you get the max possible date?

Sql Server 2005中是否有一个像GETDATE()这样的函数，让您获得可能的最大日期?

I do not want to find the highest date in a table. I want to get the max possible date that sql server will be able to store.

我不想在表格中找到最高的日期。我希望得到sql server能够存储的最大日期。

Basically, I want an expiration date of never

基本上，我想要一个永不过期的日期

7 个解决方案

#1

The documentation says the range is January 1, 1753, through December 31, 9999.

文件说，范围是1753年1月1日到9999年12月31日。

I don't think there is a built in function, but you could create one that returns the maximum datetime value.

我不认为有内置函数，但您可以创建一个返回最大datetime值的函数。

CREATE FUNCTION fn_max_date
RETURNS datetime
AS
return cast('12/31/9999 23:59:59.9999' as datetime)

#2

In my SQL Server 2008 r2, I get these odd results (not that I'm ever going to miss those 3 milleseconds)

在我的SQL Server 2008 r2中，我得到了这些奇怪的结果(不是说我将会错过这3个milleseconds)

SELECT cast('12/31/9999 23:59:59.997' as datetime) --RETURNS '9999-12-31 23:59:59.997'
SELECT cast('12/31/9999 23:59:59.998' as datetime) --RETURNS '9999-12-31 23:59:59.997'
SELECT cast('12/31/9999 23:59:59.999' as datetime) --RETURNS The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

#3

Consult the documentation.

查阅文档。

http://msdn.microsoft.com/en-us/library/ms187819.aspx

Date range
January 1, 1753, through December 31, 9999

There is no way to get the max datetime programatically.

没有办法按程序获得最大日期时间。

If so it would be listed here:

如果是，它将列在这里:

http://msdn.microsoft.com/en-us/library/ms186724.aspx

#4

I'm creating a new answer to this question just to address a couple of minor issues with scottm's otherwise good accepted answer.

我正在为这个问题创造一个新的答案，仅仅是为了解决几个与斯科特的问题有关的小问题。

According to the docs, the maximium value of the time component of a datetime is actually "23:59:59.997", so using a value of "23:59:59.999" will actually round up to midnight the following day. Where the date part is already given as 31st Dec 9999 this would attempt to round up to 1st Jan 10000 which gives an out-of-range error.
根据文档，datetime的时间组成部分的最大值实际上是“23:59:59.997”，因此使用“23:59:59.999”的值实际上会一直持续到第二天午夜。如果日期部分已经在9999年12月31日给出，则将尝试将时间增加到1月1日，这将导致超出范围的错误。

https://msdn.microsoft.com/en-us/library/ms187819(v=sql.105).aspx says:

https://msdn.microsoft.com/en-us/library/ms187819(v = sql.105). aspx说:

Date range: January 1, 1753, through December 31, 9999

日期范围:1753年1月1日至9999年12月31日

Time range: 00:00:00 through 23:59:59.997

时间范围:00:00到23:59:59.997

The date literals used are in US format, and while these are not ambiguous for 31st Dec it's best to use ISO format. Nitpicky maybe, but I find reading dates in mm/dd/yyyy format very non-intuitive, and I suspect I'm not alone.

所使用的日期文字是美国格式，虽然它们在12月31日没有歧义，但是最好使用ISO格式。可能有点挑剔，但我发现用mm/dd/yyyy格式阅读日期非常不直观，我怀疑我不是唯一这样的人。
SQL needs the body of the function to be contained in a BEGIN/END block, so as written the example could not be created.

SQL需要将函数的主体包含在开始/结束块中，这样就不能创建示例了。

So my improved version of the max DateTime function is:

因此，我对max DateTime函数的改进版本是:

CREATE FUNCTION fnMaxDateTime()
RETURNS DateTime
AS
BEGIN
    RETURN CAST('9999-12-31 23:59:59.997' AS DateTime)
END

#5

CAST seems to be dependent on the SQL Server language/culture.

CAST似乎依赖于SQL服务器语言/区域性。

On my German SQL Servers 2008 R2 and 2012 (@@language = 'Deutsch'), the following cast throws an error:

在我的德语SQL server 2008 R2和2012 (@@language = 'Deutsch')中，下面的cast抛出了一个错误:

CAST('12/31/9999 23:59:59.997' AS DATETIME)

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

将varchar数据类型转换为datetime数据类型会导致值超出范围。

Whereas this one works just fine:

而这个很好用:

CAST('31.12.9999 23:59:59.997' AS DATETIME)

I think the safest approach is to specify the format with CONVERT:

我认为最安全的方法是指定转换格式:

/* ISO 8601 */
CONVERT(DATETIME, '9999-12-31T23:59:59.997', 126)

#6

If you truly want an expiration date of "never", it might be better to store NULL rather than an arbitrary far-future date. While it is unlikely that the date will reach year 9999 without the code being "fixed", it is an illogical value to store for EndDate = never.

如果您确实想要“从不”的过期日期，那么最好存储NULL，而不是任意的远期日期。如果没有“固定”代码，日期不太可能达到9999年，但是为EndDate = never存储是不合逻辑的。

#7

January 1, 1753, through December 31, 9999

1753年1月1日到9999年12月31日

#1