Re: ORA-01861: literal does not match format string
- From: jo <jose.soares@xxxxxxxxxxxxxx>
- To: dedba@xxxxxxxxxx
- Date: Mon, 02 Apr 2012 09:19:12 +0200
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
>>> soares<jose.soares@xxxxxxxxxxxxxx>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}
>>>>
>>>>
>>>>
>>>>
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>>
>
>
>
> --
> //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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
