[SOLVED] Convert Epoch to DateTime SQL Server (Exceeds Year 2038)

Convert Epoch to DateTime SQL Server (Exceeds Year 2038)

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

Solution

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.