Search code examples
sqlsql-serverdatedatetimeunix-timestamp

Converting Unix time to DateTime 103

I have a stored procedure that joins two tables of hotel booking data, however, the API that I pull my data from uses Unix time. I need to convert this to DateTime to match with my companies fields.

Currently, my conversion looks like this.

IIF([start] IS NOT NULL, 
    CONVERT(varchar(10), [start], 103),'')  as 'ArrivalDate'

This just returns the value 1547310796 so no conversion has been done.

How do I convert the value to match 103 Date Time?

Solution

  • This should do it:

    SELECT DATEADD(second, 1547310796 - DATEDIFF(second, GETDATE(), GETUTCDATE()), '1970-01-01')

    The DATEDIFF(second, GETDATE(), GETUTCDATE()) part will give you how far behind you are from UTC time. You need to subtract that many seconds from the UTC timestamp to get the local time.