CODEWITHSUNDEEP
sqloracle11g
ankitpandey
ankitpandey

Reputation: 359

Convert Number into Date format in oracle

I have number column in oracle database which stores a timestamp. I want to convert this into a DATE and I have no clue on how to do it.

Below is what I am looking for, please suggest.

The value 1465484486246 should be converted to 2016/06/09 15:01:26,246 GMT

Upvotes: 1

Views: 4577

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

You can use NUMTODSINTERVAL along with to_date to achieve what you want:

TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL(col / 1000,'SECOND'),
        'YYYY/MM/DD HH24:MI:SS')

Here I assume that your timestamp column is called col. The timestamp 1465484486246 you gave us is in milliseconds, which is why I used col / 1000 in NUMTODSINTERVAL.

Upvotes: 1

Related Questions