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

Question

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.

try:

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
GO

If anyone getting below error:

Arithmetic overflow error converting expression to data type int

due to unix timestamp is in bigint (instead of int), you can use this:

SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01')
FROM TABLE

Replace the hardcoded timestamp for your actual column with unix-timestamp

Source: MSSQL bigint Unix Timestamp to Datetime with milliseconds

Like this

add the Unix (epoch) datetime to the base date in seconds

this will get it for now (2010-05-25 07:56:23.000)

 SELECT dateadd(s,1274756183,'19700101 05:00:00:000')

If you want to go reverse, take a look at this http://wiki.lessthandot.com/index.php/Epoch_Date

Test this:

Sql server:

SELECT dateadd(S, timestamp, '1970-01-01 00:00:00') 
     FROM 
your_table

MySql server:

SELECT
  from_unixtime(timestamp) 
FROM 
  your_table

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

This will do it:

declare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Using dateadd to add seconds to 1970-01-01
select [Datetime from UNIX Time] = dateadd(!precision!,@UNIX_TIME,'1970-01-01')

Instead of !precision! use: ss,ms or mcs according to the precision of the timestamp. Bigint is capable to hold microsecond precision.

If the time is in milliseconds and one need to preserve them:

DECLARE @value VARCHAR(32) = '1561487667713';

SELECT DATEADD(MILLISECOND, CAST(RIGHT(@value, 3) AS INT) - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(@value, 10) AS INT), '1970-01-01T00:00:00'))