SQLserver convert epoch date to date - error in conversion

Question

I got the following SQL to work on my machine at home. At work when I try this I get "Arithmetic overflow error converting expression to data type int"

(SELECT format(dateadd( second, Time, CAST( '1970-01-01' as datetime ) ), 'yyyyMMdd')) AS created_time FROM Table_Time_Test1

This worked with bigint as the format of the source of the data in the "Time" column

Conversion works on my home pc: 1673380730 => 20230110

The epoch time value is in the format bigint.

Does anyone know why this is happening?

Your code has a typographical error; you wrap the `SELECT` in parenthesis. If you remove those, the query runs fine. [db<>fiddle](https://dbfiddle.uk/ClahXDwt) — Thom A, Jan 11 '23 at 10:23
It wouldn't accept the expression without parentheses on my work computer. — jeebee, Jan 11 '23 at 10:45

jeebee · Answer 1 · 2023-01-11T10:57:18.210

0

I solved this by reducing the precision to minutes as per a stackoverflow solution. This is not ideal but it works for my purposes.

(SELECT format((DATEADD(MINUTE,Time/60/1000, '1/1/1970')), 'yyyyMMdd')) AS XXX,

edited Jan 11 '23 at 10:57

answered Jan 11 '23 at 10:47

jeebee

53
8

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 11 '23 at 21:46

SQLserver convert epoch date to date - error in conversion

1 Answers1