CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Brando
Brando

Reputation: 45

Oracle Date stored as a number

I know there are a lot of date conversion threads, but I haven't seen this particular one.

I'm using Oracle SQL Developer to pull a Timestamp field that has been stored as a number.

It's currently stored like this (20131203164906). I need to export it like this (12/03/2013 0:00).

This is what I have so far, but no combination I've tried seems to work. I've really tried before asking.

SELECT
TO_CHAR(TO_DATE(TABLE.timestamp,'dd-mm-rr'), 'mm/dd/yyyy hh24:mi:ss')
From XXXX.TABLE
WHERE ROWNUM < 10;

Yes, I know that oracle Datetimes should never be stored as strings/numbers. Any help would be great. Thanks.

Upvotes: 1

Views: 890

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14393

Your format mask for TO_DATE doesn't match your stated format.

Try this:

SELECT
TO_CHAR(TO_DATE(TABLE.timestamp,'YYYYMMDDHH24MISS'), 'mm/dd/yyyy hh24:mi')
From XXXX.TABLE
WHERE ROWNUM < 10;

Upvotes: 7

Related Questions