ORA-01861: literal does not match format string

From: <shweta.kaparwan_at_googlemail.com>
Date: Tue, 10 Feb 2009 06:05:58 -0800 (PST)
Message-ID: <0a7cdd4d-3f2b-4c65-97d0-b95047a112ef_at_41g2000yqf.googlegroups.com>

Hi All

Our aplication is throwing the following error :

Oracle access failed. ORA-01861: literal does not match format string

. SQL Stmt :

We have java based application.
and Oracle 9.2.0.6

Oracle recommends
to use :

to_date
to_char
to_number

to avoid such errors.

Application uses following SQL statement select * from T1 where last_update='2008-02-10 05:59:17';

I have re-produced the error at SQL prompt:

SQL>select * from T1 where last_update='2008-02-10 05:59:17'; select * from T1 where last_update='2008-02-10 05:59:17'

                                                 *

ERROR at line 1:
ORA-01861: literal does not match format string

SQL> select * from T1 where last_update=to_date('2008-02-10 05:59:17','yyyy-mm-dd hh24:mi:ss');

no rows selected

further,
SQL> show parameter nls_

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
nls_calendar                         string
nls_comp                             string
nls_currency                         string
nls_date_format                      string      dd-mon-rrrr
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      american
nls_length_semantics                 string      byte
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      ,.
nls_sort                             string
nls_territory                        string      america
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string

SQL> We cannt chnage the java code immediately to use to_date. so how to avoid such error:?

Regards

Shweta Received on Tue Feb 10 2009 - 08:05:58 CST