SQL Server function to convert Unix time to local datetime

Question

I am looking for an efficient SQL Server function (in my case 2005) to convert the unix time value to SQL Server date and time using local time (in particular, taking into account daylight saving time adjustments, i.e. not only adding 01/01/1970 in seconds )

Answer 1

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

Once you have a date, you can make dateadd per day depending on the DST state for the returned date. To test DST, you need some form of function, a sample:

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

Answer 2

?

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