Convert bigint epoch in SQL Server

Question

I run into trouble when I convert unix epoch timestamp to normal datetime in Azure Data Warehouse:

select 
    dateadd(s, convert(bigint, 2551564800), convert(datetime, '1-1-1970 00:00:00')) as bigint

Error:

Arithmetic overflow error converting expression to data type int.

The value 2551564800 is equal to 09/11/2050.

Any help will be appreciated

Answer 1

If you don't have epoch values greater than 5103129600 (which will allow you you to have dates up to 2106-02-07T06:28:13) this will work:

SELECT DATEADD(SECOND,2551564800 % 2147483647, DATEADD(SECOND,2147483647 * (V.Epoch / 2147483647),'19700101'))
FROM (VALUES(CONVERT(bigint,2551564800))) V(Epoch)