Convert milliseconds to Date in SQL Server for date after 01/01/2040

Question

I am trying to convert milliseconds to date in SQL Server for a date after 01/01/2040.

I tried this, but I'm getting an error:

Arithmetic overflow error

Code:

CREATE TABLE XYZ
(
    [ExpirationDate] float
);
    
INSERT INTO XYZ ([ExpirationDate])
VALUES
    (1301598290687),
    (2240542800000),--> this is 01/01/2040
    (2144034000000);    

SELECT
    DATEADD(MILLISECOND, 
            CAST(ExpirationDate AS bigint) % 1000, 
    DATEADD(SECOND, CAST(ExpirationDate AS bigint) / 1000, '19700101'))
FROM
    XYZ

Also tried

SELECT CAST(ExpirationDate AS DATETIME) FROM XYZ

but that's not working either.

See this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4c03a31832364b5a636f47eb66034c19

Looking for Date output like if we pass 2240542800000 ms as value then output should be date 01/01/2040

Answer 1

One method is to split the problem into date and time pieces:

select (DATEADD(DAY, 
                FLOOR(ExpirationDate / (24*60*60*1000.0) ), 
                '19700101') +
        DATEADD(MILLISECOND, CONVERT(bigint, ExpirationDate) % (24*60*60*1000), 0)
       )
from XYZ