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

Question

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

Answer 1

Is this for PHP? If so:

date('Y-m-d H:i:s', $timestamp);

Answer 2

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

Answer 3

try:

CREATE FUNCTIONdbo.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

Answer 4

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.