当纪元超过万亿秒时,将纪元转换为SQL Server中的DateTime时出错 [英] Error Converting Epoch to DateTime in SQL Server when epoch is more than a Trillion seconds
问题描述
我已经尝试过将EPOCH转换为SQL Server日期时间的许多答案。当EPOCH达到十亿秒时,它们就会工作。但是,一旦超过万亿美元,它就会破产!例如。 ->
I tried out many answers already given for converting EPOCH to SQL Server datetime. They work when the EPOCH has up to a billion seconds. But as soon as it crosses the Trillion mark it goes bust! E.g. -->
1. SELECT dateadd(MCS,1351187877744,'1970-01-01')
2. SELECT dateadd(NS,1351187877744,'1970-01-01')
3. SELECT dateadd(NANOSECOND, 1351187877744, '1970-01-01 00:00:00.0000000')
4. SELECT convert(bigint, datediff(ss, '01-01-1970 00:00:00',1351187877744))
以上所有操作均失败,并出现以下溢出错误:
将表达式转换为数据类型int的算术溢出错误。
All the above fail with the following overflow error: "Arithmetic overflow error converting expression to data type int."
当我在此网站上输入此日期时,很有趣它会返回正确的值。
Interestingly when I input this date on this site it returns the correct values.
任何建议如何以适用于任何大小(>万亿秒等)的EPOCH的方式执行此操作
Any suggestions how to do this in a way that works for EPOCHs which are of any magnitude (>trillion secs etc)
推荐答案
尝试一下-
MSDN:
DATEADD: number参数不能超出int的范围。在以下语句中,number参数超出int范围1。返回以下错误消息: 消息8115,级别16,状态2,行1。将表达式转换为数据类型int的算术溢出错误。
DATEADD: The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. The following error message is returned: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."
查询:
DECLARE
@Date DATETIME = '19700101'
, @MaxInt INT = 2147483647
, @ms BIGINT = 1351187877744
WHILE @ms != 0 BEGIN
SELECT @Date = DATEADD(ms, CASE WHEN @ms > @MaxInt THEN @MaxInt ELSE @ms END, @Date)
SELECT @ms = CASE WHEN @ms - @MaxInt < 0 THEN 0 ELSE @ms - @MaxInt END
END
SELECT @Date
输出:
2012-10-25 17:57:57.533
这篇关于当纪元超过万亿秒时,将纪元转换为SQL Server中的DateTime时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!