将纪元转换为DateTime SQL Server(超过2038年)

本文介绍了将纪元转换为DateTime SQL Server(超过2038年)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如果纪元超过2038年，如何将纪元转换为DateTime SQL Server?

将纪元转换为DateTime SQL Server 中的答案将不起作用. /p>

示例:

SELECT DATEADD(ss, 2713795200000 / 1000, '19700101')

2055年12月30日，星期四，格林尼治标准时间

解决方案

DATEADD函数将INT假定为日期的增量，要绕过INT的限制，您可以降低时元的精度，也可以稍微复杂一些代码以保持您时代的准确性.

这会将精度降低到分钟:

SELECT DATEADD(MINUTE,@YourEpoch/60/1000, '1/1/1970')

这将您的时期分为几天和几毫秒，然后将它们合并为一个日期时间

CREATE FUNCTION [dbo].[fn_EpochToDatetime] (@Epoch BIGINT) RETURNS DATETIME AS BEGIN DECLARE @Days AS INT, @MilliSeconds AS INT SET @Days = @Epoch / (1000*60*60*24) SET @MilliSeconds = @Epoch % (1000*60*60*24) RETURN (SELECT DATEADD(MILLISECOND, @MilliSeconds, DATEADD(DAY, @Days, '1/1/1970'))) END;

但是，我不确定第二种解决方案为什么不如我期望的那么精确.

How to convert Epoch to DateTime SQL Server if epoch exceeds the year 2038?

Answer in Convert Epoch to DateTime SQL Server will not work.

Example:

SELECT DATEADD(ss, 2713795200000 / 1000, '19700101')

Thu, 30 Dec 2055 16:00:00 GMT

解决方案

DATEADD function assumes an INT as an increment to your date, to bypass the limitation of INT you can either reduce the precision of your epoch, or do a slightly complex code to retain the precision of your epoch.

This reduces the precision to minutes:

SELECT DATEADD(MINUTE,@YourEpoch/60/1000, '1/1/1970')

This one splits your epoch to days and milliseconds and then combines them in a datetime

CREATE FUNCTION [dbo].[fn_EpochToDatetime] (@Epoch BIGINT) RETURNS DATETIME AS BEGIN DECLARE @Days AS INT, @MilliSeconds AS INT SET @Days = @Epoch / (1000*60*60*24) SET @MilliSeconds = @Epoch % (1000*60*60*24) RETURN (SELECT DATEADD(MILLISECOND, @MilliSeconds, DATEADD(DAY, @Days, '1/1/1970'))) END;

However, I'm not quite sure why the 2nd solution is not as precise as I expect it to be.