SQL Server function to convert Unix time to local datetime

Question

I'm looking for an efficient SQL Server function (in my case 2005) to convert a unix time value into a SQL Server datetime, using local time (particularly taking account of summertime adjustments - i.e not just adding 01/01/1970 in seconds)

Answer 1

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

After you have the date, you can now do dateadd on the date depending on the DST state for the returned date. To check for DST you need some form of function, 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

You need a simular function to find when DST ends, take a look at this site for more info: http://www.mssqltips.com/tip.asp?tip=1372

Answer 2

better?

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