Convert a string date into datetime in Oracle

26
votes

How can I convert this string date to datetime in oracle.

2011-07-28T23:54:14Z

Using this code throws an error:

TO_DATE('2011-07-28T23:54:14Z',  'YYYY-MM-DD HH24:MI:SS')

How can this be done?

Error report:
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Update:-

TO_DATE('2011-07-28T23:54:14Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

I only see the date not time in the column

28-JUL-11
3
sqloracleora-01861
Your format doesn't have T and Z letters, or am I wrong? Try to parse 2011-07-28 23:54:14, if it will pass, then use regex to normalize your input strings. – JMelnik
@p.campbell but in my case. String date is in different format with t and z so is there any way to convert that directly to datetime format. Or I have to parse it to make it more clearer then convert that to datetime format.. – arsenal

3 Answers

52
votes

Try this: TO_DATE('2011-07-28T23:54:14Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

1
votes

Hey I had the same problem. I tried to convert '2017-02-20 12:15:32' varchar to a date with TO_DATE('2017-02-20 12:15:32','YYYY-MM-DD HH24:MI:SS') and all I received was 2017-02-20 the time disappeared

My solution was to use TO_TIMESTAMP('2017-02-20 12:15:32','YYYY-MM-DD HH24:MI:SS') now the time doesn't disappear.

-1
votes

You can use a cast to char to see the date results 

 select to_char(to_date('17-MAR-17 06.04.54','dd-MON-yy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') from dual;