Re: ORA-01861: literal does not match format string
- From: De DBA <dedba@xxxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx, Oracle-L@xxxxxxxxxxxxx
- Date: Sat, 31 Mar 2012 22:36:24 +1000
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
Other related posts:
