0

I have timestamp column:

MAX_LOAD_DATE_TS  TIMESTAMP(6)

My load_dates comes formatted with T in between date and time.

2014-07-02T10:46:22

Is there a way to convert this to legit timestamp on oracle?

Angelina
  • 2,175
  • 10
  • 42
  • 82

2 Answers2

3

You can embed literals in the format string if they're double-quoted:

select to_timestamp('2014-07-02T10:46:22', 'YYYY-MM-DD"T"HH24:MI:SS') from dual;

TO_TIMESTAMP('2014-07-02T10:46:22','YYYY-MM-DD"T"HH24:MI:SS')
-------------------------------------------------------------
02-JUL-14 10.46.22.000000000

That works with to_date() as well as to_timestamp(), and with to_char() going the other way.

This is mentioned in the documentation:

Punctuation and Character Literals in Datetime Format Models

You can include these characters in a date format model:

  • Punctuation such as hyphens, slashes, commas, periods, and colons
  • Character literals, enclosed in double quotation marks

These characters appear in the return value in the same location as they appear in the format model.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

You can use the appropriate format string as well as the CAST function, as below:

SELECT 
        CAST(TO_DATE('2014-07-02T10:46:22', 'YYYY-MM-DD"T"HH24:MI:SS') AS TIMESTAMP(6)) AS timestamp_value
FROM table;

Related question

Community
  • 1
  • 1
Joseph B
  • 5,519
  • 1
  • 15
  • 19
  • You would need to double-quote the "T" in the format mask. Otherwise, you'd get an ORA-01821 date format not recognized error since Oracle would try to interpret the T as one of the valid format masks. – Justin Cave Jul 08 '14 at 17:57
  • @JustinCave Yes, noted. I have updated the answer above. – Joseph B Jul 08 '14 at 17:58
  • 1
    Not sure why you'd use `cast(to_date() as timestamp)` rather than just `to_timestamp()`? – Alex Poole Jul 08 '14 at 17:59
  • 2
    @AlexPoole Simply because I did not know better. Thank you for the note. – Joseph B Jul 08 '14 at 18:01