2

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
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
  • possible duplicate of [How to convert "1985-02-07T00:00:00.000Z" (ISO8601) to a date value in Oracle?](http://stackoverflow.com/questions/8654292/how-to-convert-1985-02-07t000000-000z-iso8601-to-a-date-value-in-oracle) – Lalit Kumar B Apr 07 '15 at 05:43
  • The answer may be the same ("use double quotes") but the question is different. It's not obvious reading the manual how to specify character literals. – Mark Harrison Apr 07 '15 at 08:34
  • Well, regarding quoting literals in Oracle, it is the same good old way to use double-quotation marks. Which works in the date format too. Regarding the duplicate question, I just pasted your date format in google, and that SO link came at very first in Google ;-) – Lalit Kumar B Apr 07 '15 at 08:40
  • Yeah, well the documentation could do a better job in specifying that, because I read http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions203.htm#SQLRF06132 and associated and couldn't find it. Congratulations on your google skills, but I wasn't concerned are about the specific date format, I was concerned about how to_date quotes literal characters. – Mark Harrison Apr 07 '15 at 08:44
  • It is documented http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA. I added this as an answer so that it is visible to others clearly. Thanks for pointing this out, I revisited the docs. – Lalit Kumar B Apr 07 '15 at 08:52

2 Answers2

5

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
Politank-Z
  • 3,653
  • 3
  • 24
  • 28
1

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"')
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124