ankitpandey ankitpandey - 5 months ago 35
SQL Question

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

Answer

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.