用于将Unix时间转换为本地日期时间的SQL Server函数

SELECT DATEADD(second, @ts, {d '1970-01-01'}) as MSSQLdatetime

有了日期之后,您现在可以根据返回日期的DST状态对日期执行日期添加。要检查DST,您需要某种形式的函数,示例:

CREATE function [dbo].[fn_GetDaylightSavingsTimeStart]
(@Year varchar(4))
RETURNS smalldatetime
as
begin
 declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime
 set @DTSStartWeek = '03/01/' + convert(varchar,@Year)
 return case datepart(dw,@DTSStartWeek)
 when 1 then
  dateadd(hour,170,@DTSStartWeek)
 when 2 then
  dateadd(hour,314,@DTSStartWeek)
 when 3 then
  dateadd(hour,290,@DTSStartWeek)
 when 4 then
  dateadd(hour,266,@DTSStartWeek)
 when 5 then
  dateadd(hour,242,@DTSStartWeek)
 when 6 then
  dateadd(hour,218,@DTSStartWeek)
 when 7 then
  dateadd(hour,194,@DTSStartWeek)
 end
end

您需要一个模拟器函数来查找DST何时结束,请查看此站点以获取更多信息: http://www.mssqltips.com/tip.asp?tip=1372

更好?

CREATE FUNCTION [dbo].[UnixTimestampToGMTDatetime] 
(@UnixTimestamp bigint)
RETURNS datetime
AS
BEGIN
       DECLARE @GMTDatetime datetime
       select @GMTDatetime = 
       CASE
       WHEN dateadd(ss, @UnixTimestamp/1000, '1970-01-01') 
       BETWEEN 
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 4) % 7)) + ' 01:00:00', 20)
       AND
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 1) % 7)) + ' 02:00:00', 20)
       THEN Dateadd(hh, 1, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       ELSE Dateadd(hh, 0, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       END
RETURN @GMTDatetime    
END