Convert Epoch to DateTime SQL Server (Exceeds Year

Question

How to convert Epoch to DateTime SQL Server if epoch exceeds the year 2038?

Answer in Convert Epoch to DateTime SQL Server will not work.

Example:

SELECT DATEADD(ss, 2713795200000 / 1000, '19700101')

Thu, 30 Dec 2055 16:00:00 GMT

Answer 1

DATEADD function assumes an INT as an increment to your date, to bypass the limitation of INT you can either reduce the precision of your epoch, or do a slightly complex code to retain the precision of your epoch.

This reduces the precision to minutes:

SELECT DATEADD(MINUTE,@YourEpoch/60/1000, '1/1/1970')

This one splits your epoch to days and milliseconds and then combines them in a datetime

CREATE FUNCTION [dbo].[fn_EpochToDatetime] (@Epoch BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Days AS INT, @MilliSeconds AS INT
    SET @Days = @Epoch / (1000*60*60*24)
    SET @MilliSeconds = @Epoch % (1000*60*60*24)

    RETURN (SELECT DATEADD(MILLISECOND, @MilliSeconds, DATEADD(DAY, @Days, '1/1/1970')))
END;

However, I'm not quite sure why the 2nd solution is not as precise as I expect it to be.

Answer 2

create a function to convert epoch to datetime and use them in your query like below

create FUNCTION [dbo].[from_unixtime] (@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;

and then use this function in your query