如果纪元超过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.
更多推荐
将纪元转换为DateTime SQL Server(超过2038年)
发布评论