CODEWITHSUNDEEP
oracle-databasedate
Mark Harrison
Mark Harrison

Reputation: 304554

Oracle: How can I specify character literals in TO_DATE conversions?

How do I specify character literals in a date specification? In the second example, I would like to skip the T and Z. 

select to_date('2015-04-06 19:56:30', 'YYYY-MM-DD HH24:MI:SS') from dual;

    2015-04-06 19:56:30                                  

select to_date('2015-04-06 19:56:30', 'YYYY-MM-DDTHH24:MI:SSZ') from dual;

    ORA-01821: date format not recognized

Upvotes: 2

Views: 1177

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

From 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.

Following the documentation, enclosing the character literals in double-quotation marks will work in the format model.

TO_DATE('2015-04-06T19:56:30Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

Upvotes: 1

Politank-Z
Politank-Z

Reputation: 3719

You can enclose the literals in double quotes:

SQL> select to_date('2015-04-06T19:56:30Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;

TO_DATE('2015-04-0
------------------
06-APR-15

Upvotes: 5

Related Questions