在SQL中将纪元转换为DateTime [英] Epoch conversion to DateTime in SQL
本文介绍了在SQL中将纪元转换为DateTime的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我提供了一个SQL函数,可以将纪元转换为日期时间.
I have a SQL function that was provided which convert epoch to a date time.
此sql函数在1970年1/3月之前的日期不起作用的问题.是否有人有任何想法使此作品的日期小于1970年.
The issues that this sql function does not work for dates before 1/3/1970. Does anyone have any ideas to make this work for dates less than 1970.
DECLARE @total bigint
--if greater than 12/31/9999 return null
IF @total > 253402232400000
RETURN NULL
--if less than or equal 1/3/1970 return null
IF @total <= 18000000
RETURN NULL
DECLARE @seconds int = @total / 86400000;
DECLARE @milliseconds int = @total % 86400000;
DECLARE @result datetime = '1970-1-1';
SET @result = DATEADD(DAY, @seconds,@result);
SET @result = DATEADD(MILLISECOND, @milliseconds,@result);
RETURN @result;
推荐答案
尝试一下.
应该适用于从0001-01-01T00:00:00.000
到9999-12-31T23:59:59.999
的所有日期.
Try this.
Should work for all dates from 0001-01-01T00:00:00.000
through 9999-12-31T23:59:59.999
.
-- UnixTimeToDateTime2
--
-- Parameter: 64-bit integer
-- Number of milliseconds
-- since 1970-01-01T00:00:00.000
-- May be negative before 1970
--
-- Returns datetime2
-- Works with all values in
-- range 0001-01-01T00:00:00.000
-- through 9999-12-31T23:59:59.999
-- Returns NULL if parameter is out of range
create function dbo.UnixTimeToDateTime2(@x bigint)
returns datetime2
as
begin
return
case
-- If the parameter is out of range,
-- return NULL
when ( @x < -62135596800000 )
or ( @x > 253402300799999 ) then null
else
-- We would like to add this number of milliseconds
-- directly to 1970-01-01T00:00:00.000, but this
-- can lead to an overflow.
-- Instead we break the addition into a number of days
-- and a number of milliseconds.
-- To get the number of days, we divide by the number
-- of milliseconds in a day. Then add the remainder.
dateadd ( millisecond,
@x % 86400000,
dateadd ( day,
@x / 86400000,
cast( '1970-01-01T00:00:00.000'
as datetime2 )) )
end
end
这篇关于在SQL中将纪元转换为DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文