sql server - Convert numeric to datetime

Question

I'm querying a SQL Server table that has this column:

dateCreated(numeric(19, 0), null)

It appears this is UNIX epoch time. I want to select everything from the table and convert the dateCreated column to something appropriate for a report extract. I've tried the following via some other posts I found:

How to convert Epoch time to date?

SELECT 
    DATEADD(S, t.dateCreated, CAST('1970-01-01' as datetime)), 
    t.user, t.machine  
FROM
    table t

Any variant of this produces an error

Arithmetic overflow error converting expression to data type int

How can I convert this column to datetime format?

question from:https://stackoverflow.com/questions/65839362/convert-numeric-to-datetime

Answer 1

That happen because for at least one row in t, t.dateCreated is bigger than 2147483647.

2147483647 is the max value of int data type in SQL Server.

2147483647 is 2038-01-19 03:14:07 in EPOCH time.

If t.dateCreated is really EPOCH time and t.dateCreated has values higher than 2038-01-19 03:14:07, that query is not going to work.

Maybe you should check:

• If t.dateCreated is really EPOCH time.
• The rows where t.dateCreated > 2147483647, just in case of some outliers.

Another thing to check, is if the EPOCH time is expressed in milliseconds.

In that case, the query should be

SELECT 
DATEADD(S, t.dateCreated / 1000, CAST('1970-01-01' as datetime)), 
    t.user, t.machine   
FROM
    table t

But you are going to miss the milliseconds.