Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Error - ORA-01861: literal does not match format string"
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
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 BortelReceived on Fri Apr 29 2005 - 06:54:50 CDT