CODEWITHSUNDEEP
sqldatabaseoracle-databasedatedatetime
Ninja
Ninja

Reputation: 183

Convert unix timestamp to Date and DateTime - SQL/ORACLE

I have a column of NUMBER(15) datatype which stores unix timestamps. I am trying to get a date and datetime from it. I have been able to get datetime from it but not a date. e.g. 1456342438 is the value i am trying to convert to date and datetime.

Either of these queries give me a DateTime:

select TO_DATE('1970-01-01','YYYY-MM-DD') + numtodsinterval(1456342438,'SECOND') from dual;

select TO_DATE('19700101000000','YYYYMMDDHH24MISS') + numtodsinterval(1456342438,'SECOND') from dual;

How can i get a date from this value?

Upvotes: 2

Views: 8946

Answers (1)

Ninja
Ninja

Reputation: 183

select to_date('01/01/1970','mm/dd/yyyy') + numtodsinterval(1456342438,'SECOND'), 
TRUNC(to_date('01/01/1970','mm/dd/yyyy') + numtodsinterval(1456342438,'SECOND')) from dual;

This worked.

Upvotes: 2

Related Questions