-1

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
deskFan
  • 31
  • 1
  • 4

1 Answers1

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.

EmiFala
  • 51
  • 1
  • 6
  • Thank you! I already put some of the values into a website to convert epoch time and it verified so I was absolutely certain it was epoch time but it seems it is in fact in milliseconds. Adding the /1000 did the trick. – deskFan Jan 22 '21 at 22:06