FAQ
**

Hi all,

**
**

I don't understand this error:

**
**

ORA-01861: literal does not match format string

**
**

'UPDATE unita_aziendale SET ts_ultima_modifica=CURRENT_TIMESTAMP, data_inizio=:data_inizio WHERE unita_aziendale.id = :unita_aziendale_id' {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}

**
**

all things seems ok to me. What's wrong?

**
**

j

**

Search Discussions

  • Rjamya at Mar 29, 2012 at 3:33 pm
    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 = :unita_aziendale_id'
    {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}

    --
    http://www.freelists.org/webpage/oracle-l
  • Jo at Mar 29, 2012 at 5:18 pm
    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 = :unita_aziendale_id'
    {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Jose Soares _/_/
    Sferacarta Net
    Via Bazzanese 69 _/_/ _/_/_/
    40033 Casalecchio di Reno _/_/ _/_/ _/_/
    Bologna - Italy _/_/ _/_/ _/_/
    Ph +39051591054 _/_/ _/_/ _/_/ _/_/
    fax +390516131537 _/_/ _/_/ _/_/ _/_/
    web:www.sferacarta.com _/_/_/ _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • De DBA at Mar 31, 2012 at 12:37 pm
    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:

    DECLARE
    l_date DATE;
    BEGIN
    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 11.2.0.1 test database:

    SQL> select * from nls_session_parameters;

    PARAMETER |VALUE
    =========================|=========================
    NLS_LANGUAGE |ENGLISH
    NLS_TERRITORY |AUSTRALIA
    NLS_CURRENCY |$
    NLS_ISO_CURRENCY |AUSTRALIA
    NLS_NUMERIC_CHARACTERS |.,
    NLS_CALENDAR |GREGORIAN
    *NLS_DATE_FORMAT |DD/MON/RR
    *...<snip>

    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 = :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.

    Cheers,
    Tony
    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 soareswrote:

    'UPDATE unita_aziendale SET ts_ultima_modifica=CURRENT_TIMESTAMP,
    data_inizio=:data_inizio WHERE unita_aziendale.id = :unita_aziendale_id'
    {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}

    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Jo at Apr 2, 2012 at 7:20 am
    Same rule to insert a timestamp:

    insert into test values(DATE '2012-01-12', TIMESTAMP '2012-12-01 12:12:22')

    I think it is the Standard SQL.
    j

    De DBA wrote:
    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:

    DECLARE
    l_date DATE;
    BEGIN
    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 11.2.0.1 test database:

    SQL> select * from nls_session_parameters;

    PARAMETER |VALUE
    =========================|=========================
    NLS_LANGUAGE |ENGLISH
    NLS_TERRITORY |AUSTRALIA
    NLS_CURRENCY |$
    NLS_ISO_CURRENCY |AUSTRALIA
    NLS_NUMERIC_CHARACTERS |.,
    NLS_CALENDAR |GREGORIAN
    *NLS_DATE_FORMAT |DD/MON/RR
    *...<snip>

    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 = :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.

    Cheers,
    Tony
    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 soareswrote:


    'UPDATE unita_aziendale SET ts_ultima_modifica=CURRENT_TIMESTAMP,
    data_inizio=:data_inizio WHERE unita_aziendale.id = :unita_aziendale_id'
    {'data_inizio': u'1996-12-10', 'unita_aziendale_id': 319}


    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l


    --
    Jose Soares _/_/
    Sferacarta Net
    Via Bazzanese 69 _/_/ _/_/_/
    40033 Casalecchio di Reno _/_/ _/_/ _/_/
    Bologna - Italy _/_/ _/_/ _/_/
    Ph +39051591054 _/_/ _/_/ _/_/ _/_/
    fax +390516131537 _/_/ _/_/ _/_/ _/_/
    web:www.sferacarta.com _/_/_/ _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 29, '12 at 3:24p
activeApr 2, '12 at 7:20a
posts5
users3
websiteoracle.com

3 users in discussion

Jo: 3 posts Rjamya: 1 post De DBA: 1 post

People

Translate

site design / logo © 2022 Grokbase