Request:

SELECT to_date(DH.LAST_STATE_CHANGED, 'dd.mm.yy') FROM os_doc.document_header dh

Mistake:

ORA-01861: literal does not match the format of the string

    2 answers 2

    Different NLS_DATE_FORMAT in each of the tools.

      The last_state_changed field in the SQL expression is used incorrectly. Perhaps in some kind of tool it will work, but do not rely on chance.
      Correctly:

       select to_char (dh.last_state_changed, 'dd.mm.yy') from os_doc.document_header dh

      Why is that?

      The document_header.last_state_changed field has a DATE data type (I do not consider the declaration as VARCHAR2 , since this would be most likely an error when developing the database schema):

      1. Data is first implicitly converted to VARCHAR2 . because overloaded function to_date (dt date, format varchar2) missing as meaningless.

      2. Further, now explicitly , the data is converted to the DATE data type using the format mask 'dd.mm.yy' .

      3. Finally, since the internal representation of the DATE data type is not quite readable, it is implicitly converted again into a character representation.

      In all cases of implicit conversion, the value NLS_DATE_FORMAT , which can be set in the Windows registry, environment variable or in the settings of the tool used.

      If this value does not coincide with the format mask specified explicitly, various conversion errors occur, depending on the place where the character literal does not match the format mask:

      ORA-01861: literal does not match format string
      ORA-01843: not a valid month
      ORA-01858: a non-numeric character was found where a numeric was expected
      ORA-01840: input value not long enough for date format