Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Error - ORA-01861: literal does not match format string"

Re: Oracle Error - ORA-01861: literal does not match format string"

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 29 Apr 2005 13:54:50 +0200
Message-ID: <d4t72t$nuh$1@news6.zwoll1.ov.home.nl>


Abhishek wrote:
> In my application working on oracle 10g database and Red Hat Linux
> Advanced Server 2.1 on HP Itanium pcs.
>
> When i use in my query statements like
>
> update table1 set colmn_dt = to_date(sysdate,'DD/MON/YYYY');
>
> it is giving me the following error.
>
> ORA-01861: literal does not match format string"
>
>
> The resaon for this is i can understand that i m trying to use a date
> type column in a to_date function.
>
> What i cannot undertand is that why did this has not given me the
> error uptil now(the application has been running for one year approx)
> and now also it is giving me the error for some cases only, otherwise
> this query runs fine.
>
>
> i hope some one can describe me the unconsistancy of the given query.
>
> thanx in advance.

The question is: is column_dt a true date column (the data type is actually DATE)?
If so, there's no need to apply any format mask, as sysdate is already a date:
update table1 set colmn_dt = sysdate;
will do it.

The format mask, used in your update statement must first convert sysdate to a string, then apply the format mask ( the 'DD/MON/YYYY' bit) and then rework to a date (the to_date() bit).

The error occurs in the first conversion; for this, oracle needs to know in what format sysdate is presented. It "knows" that by looking in the environment at the NLS_DATE_FORMAT. Change that in 'DD/MON/YYYY' and you will never have the error - anything else will produce an error:

SQL> alter session set nls_date_format='DD/MON/YYYY'; Session altered.

SQL> select to_date(sysdate,'DD/MON/YYYY') from dual; TO_DATE(SYS



29/APR/2005 SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; Session altered.

SQL> select to_date(sysdate,'DD/MON/YYYY') from dual; select to_date(sysdate,'DD/MON/YYYY') from dual

               *
ERROR at line 1:
ORA-01843: not a valid month

Not your error; yours has to do with how you spell the MON part - I suspect the above is a simplification of the actual update statement.

01861, 00000, "literal does not match format string"

// *Cause:  Literals in the input must be the same length as literals in
//  the format string (with the exception of leading whitespace).  If the
//  "FX" modifier has been toggled on, the literal must match exactly,
//  with no extra whitespace.
// *Action: Correct the format string to match the literal.
-- 
Regards,
Frank van Bortel
Received on Fri Apr 29 2005 - 06:54:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US