Convert unix time format to date/time format in oracle sql

Question

I have a column called data_time (varchar) and there are about 200 thousand rows. I would like to convert those rows to ordinary date/time instead.

I have tried this with no luck.

example value in a row: 927691200000000

SELECT * TO_DATE('19700101',yyyymmdd') + ((date_time/1000)/24/60/60) thedate 2 FROM table1

I am new to SQL and help is appreciated!

Thank you.

What does "no luck" mean? Did you get an error? If so, what error? There appear to be a couple of obvious syntax errors in the query you posted but it's not clear whether those errors are your problem or whether you have transcribed the SQL statement incorrectly (the * and the 2 are both errors). What date does the number you provided represent? — Justin Cave
– Justin Cave, Commented Dec 11, 2013 at 17:12

Mark J. Bobak · Accepted Answer · 2013-12-11 17:21:38Z

3

I cleaned up the obvious syntax errors, added some date formatting, and just hardcoded the one sample value you provided, thus:

SELECT to_char(TO_DATE('19700101','yyyymmdd') + ((927691200000000/1000)/24/60/60),'DD-MON-YYYY') thedate  FROM dual;

And that yielded:

ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Which suggests that your unix time is (probably?) expressed in microseconds, not milliseconds.

So, I modified the query thus:

SELECT to_char(TO_DATE('19700101','yyyymmdd') + ((927691200000000/1000000)/24/60/60),'DD-MON-YYYY') thedate  FROM dual;

Which returns:

THEDATE
-----------
26-MAY-1999

Which I assume to be correct?

Hope that helps....

answered Dec 11, 2013 at 17:21

Mark J. Bobak

14.5k6 gold badges45 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1