在 ms sql server 2005+ 中查找最大可能日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/548353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
finding max possible date in ms sql server 2005+
提问by scottm
Is there a function like GETDATE()in Sql Server 2005 that let's you get the max possible date?
是否有类似于GETDATE()Sql Server 2005的函数让您获得最大可能的日期？
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
基本上，我想要一个永不过期的日期
回答by scottm
The documentationsays 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.
我认为没有内置函数，但您可以创建一个返回最大日期时间值的函数。
CREATE FUNCTION fn_max_date
RETURNS datetime
AS
return cast('12/31/9999 23:59:59.9999' as datetime)
回答by Tommy Heath
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 毫秒）
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.
回答by marsze
CAST seems to be dependent on the SQL Server language/culture.
CAST 似乎依赖于 SQL Server 语言/文化。
On my GermanSQL Servers 2008 R2 and 2012 (@@language = 'Deutsch'), the following cast throws an error:
在我的德语SQL Server 2008 R2 和 2012 ( @@language = 'Deutsch') 上，以下强制转换会引发错误：
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)
SOLUTION
解决方案
I think the safest approach is to specify the format with CONVERT:
我认为最安全的方法是使用 CONVERT 指定格式：
/* ISO 8601 */
CONVERT(DATETIME, '9999-12-31T23:59:59.997', 126)
回答by Jim Counts
Consult the documentation.
查阅文档。
http://msdn.microsoft.com/en-us/library/ms187819.aspx
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:
如果是这样，它将在此处列出：
回答by beercohol
I'm creating a new answer to this question just to address a couple of minor issues with scottm's otherwise good accepted answer.
我正在为这个问题创建一个新答案，只是为了解决 scottm 其他很好的公认答案的几个小问题。
- According to the docs, the maximium value of the timecomponent of a
datetimeis 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.
- 根据文档，a的时间分量的最大值
datetime实际上是“23:59:59. 997”，因此使用“23:59:59.999”的值实际上将四舍五入到第二天的午夜。如果日期部分已经给出为 31st Dec 9999，这将尝试向上舍入到 1st Jan 10000，这会导致超出范围的错误。
https://msdn.microsoft.com/en-us/library/ms187819(v=sql.105).aspxsays:
https://msdn.microsoft.com/en-us/library/ms187819(v=sql.105).aspx说：
Date range: January 1, 1753, through December 31, 9999
Time range: 00:00:00 through 23:59:59.997
日期范围：1753 年 1 月 1 日至 9999 年 12 月 31 日
时间范围：00: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.
SQL needs the body of the function to be contained in a BEGIN/END block, so as written the example could not be created.
使用的日期文字采用美国格式，虽然这些对于 12 月 31 日没有歧义，但最好使用 ISO 格式。也许挑剔，但我发现以 mm/dd/yyyy 格式读取日期非常不直观，而且我怀疑我并不孤单。
SQL 需要将函数体包含在 BEGIN/END 块中，因此无法创建示例。
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
回答by Taylor Gerring
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 存储起来是一个不合逻辑的值。
回答by Calvin Allen
January 1, 1753, through December 31, 9999
1753 年 1 月 1 日至 9999 年 12 月 31 日
