sqlsql-servert-sqldatetimeunix-timestamp

How can I convert bigint (UNIX timestamp) to datetime in SQL Server?


How can I convert UNIX timestamp (bigint) to DateTime in SQL Server?


Solution

  • This worked for me:

    Select
        dateadd(S, [unixtime], '1970-01-01')
    From [Table]
    

    In case any one wonders why 1970-01-01, This is called Epoch time.

    Below is a quote from Wikipedia:

    The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.

    The Year 2038 Problem

    Furthermore the DataAdd function takes an int for the seconds to add. So if you are trying to add more than 2147483647 seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.

    Declare @t as bigint = 4147483645
    
    Select (@t / @oneyear) -- Years to add
    Select (@t % @oneyear) -- Remaining seconds to add
    
    -- Get Date given a timestamp @t
    Declare @oneyear as int = 31622400
    Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))
    

    This will let you convert a timestamp that represents a year greater than 2038.