Re: ORA-01861: literal does not match format string

From: De DBA <>
Date: Sat, 31 Mar 2012 22:36:24 +1000
Message-ID: <>

Funnily enough, Steven Feuerstein just yesterday sent out his "Oracle PL/SQL Programming Newsletter", in which he touched upon exactly this problem. There is a date literal in Oracle which takes the date format 'YYYY-MM-DD', regardless of NLS settings. Quoted from the newsletter:

    ... So Oracle now (actually, since Oracle9!) supports the ANSI date literal, which means that you can assign a value to a date as follows:

       l_date DATE;
        l_date := DATE '2011-02-15';
    END;     In other words: the keyword DATE follows by a literal string in the form YYYY-DD-MM. You have no choice in this format; it cannot be changed by changing NLS settings. In addition, the ANSI date literal contains no time portion....

This listing is from my test database:

SQL> select * from nls_session_parameters;

PARAMETER                |VALUE
NLS_CURRENCY             |$


17 rows selected.

SQL> create table test ( the_date date ) ;

Table created.

  • using to_date always works SQL> insert into test values ( to_date ( '1 January 2012', 'DD Month YYYY' ) ) ;

1 row created.

  • the NLS_FORMAT is implicitly converted (note that / and - are equivalent) SQL> insert into test values ( '2-JAN-2012' ) ;

1 row created.

  • other formats cause the ORA-1861 to happen SQL> insert into test values ( '2012-1-3' ) ; insert into test values ( '2012-1-3' ) * ERROR at line 1: ORA-01861: literal does not match format string
  • ANSI literal with format YYYY-MM-DD works, even though NLS_FORMAT is DD/MON/RR SQL> insert into test values ( *date *'2012-1-3' ) ;

1 row created.

The OP's statement could therefore be written as:

UPDATE unita_aziendale

    SET ts_ultima_modifica = CURRENT_TIMESTAMP,
      , data_inizio        =*date*  :data_inizio
  WHERE = :unita_aziendale_id

as long as data_inizio will be in the ANSI date format. I'm not sure about the unicode prefix though, it is probably not needed here as the date is not stored as characters anyway.


On 30/03/12 03:03, jo wrote:
> Yes Rjamya, you are right, this is a nls_date_format.
> Thanks for your help.
> j
> rjamya wrote:

>> is your NLS_DATE_FORMAT set to YYYY-MM-DD or YYYY-DD-MM (I can't figure out
>> since 12-10 and 10-12 are both valid dates) ? if not, then you have a
>> problem that needs to be fixed.
>> On Thu, Mar 29, 2012 at 11:25 AM, jose soares<>wrote:
>>> 'UPDATE unita_aziendale SET ts_ultima_modifica=CURRENT_TIMESTAMP,
>>> data_inizio=:data_inizio WHERE = :unita_aziendale_id'
>>> {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}
>> --

Received on Sat Mar 31 2012 - 07:36:24 CDT

Original text of this message