CODEWITHSUNDEEP
javadatabaseoracle-databasedate
Tamas G.
Tamas G.

Reputation: 737

ORA-01861: literal does not match format string error isn't always shown in similar cases

I have a select SQL query which contains this:

to_date('292278994-08-17 08:12:55.807', 'yyyy-mm-dd hh:mi:ss')

The value of this date was generated with this java code line:

Date maxDate = new Date(Long.MAX_VALUE);

It is obvious that year 292278994 doesn't match 'yyyy'. The problem: running my query causes

ORA-01861: literal does not match format string

on the main database but not on the backup database. The databases are fully equivalent, the backup is of course the copy of the main one. Both of them are an Oracle 11.2. I guess it depends on some configuration but I couldn't find anything.

Does anyone know the cause of this? Thanks!

Upvotes: 0

Views: 612

Answers (2)

Patrick Marchand
Patrick Marchand

Reputation: 3445

Check the explain plans of your actual query as it looks like you've got different plans and one is "short circuiting" the conditions; it doesn't actually get around to looking at the date format.

Here's what I mean:

select *
from dual
where 1=0
AND sysdate < to_date('292278994-08-17 08:12:55.807', 'yyyy-mm-dd hh:mi:ss')

vs

select *
from dual
where 1=0
OR sysdate < to_date('292278994-08-17 08:12:55.807', 'yyyy-mm-dd hh:mi:ss')

The first query above will return 0 rows and will not report an error (at least it doesn't for me), yet the second one does. That's because the database can evaluate the 1=0 (false) first and doesn't need to check the second condition at all because FALSE AND (TRUE or FALSE) is always false whereas with an OR, it's possible that the second condition returns TRUE and the WHERE clause returns a TRUE result.

For anyone interested, in the plan of the first query, I get this:

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL AND SYSDATE@!<TO_DATE('292278994-08-17 
              08:12:55.807','yyyy-mm-dd hh:mi:ss'))

(Note that it's not 1=0 anymore but rather NULL IS NOT NULL)

and in the second one, it knows the 1=0 can be omitted entirely:

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYSDATE@!<TO_DATE('292278994-08-17 
              08:12:55.807','yyyy-mm-dd hh:mi:ss'))

Upvotes: 3

kevinskio
kevinskio

Reputation: 4551

This will show the parameters that could affect the dates. try this on both databases and compare

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT';

Upvotes: 0

Related Questions