Literal does not match format string for Oracle SQL to_date on a string column

Question

Dear SQL Gurus from Stack Overflow:

Environment: Oracle

I'm trying to understand why I can't do a to_date selection on a table column that contains strings. Note tableZ with a column of name Value in the example below contains a bunch of strings, some of which are the correct format, for example 6/20/2010 00:00:00.

tableZ

| Value              |
| __________________ |
| 6/21/2010 00:00:00 |
| Somestring         |
| Some Other strings |
| 6/21/2010 00:00:00 |
| 6/22/2010 00:00:00 |

The following works

SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') somedate 
          FROM tableX a, tableY b, tableZ c 
         WHERE Lower(a.name) = 'somedate' 
           AND a.id = b.other_id 
           AND b.id = c.new_id

This returns something like (which is good):

| somedate            |
| __________________  |
| 21.06.2010 00:00:00 |
| 21.06.2010 00:00:00 |
| 22.06.2010 00:00:00 |

The following does not work

SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') somedate 
          FROM properties$aud a, template_properties$aud b, consumable_properties$aud c 
         WHERE Lower(a.name) = 'somedate' 
           AND a.id = b.property_id 
           AND b.id = c.template_property_id 
           AND To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') IS NOT NULL

Comes back with:

ORA-01861: literal does not match format string

What am I missing here? Just a quick note:

 ...
AND b.id = c.template_property_id 
AND To_Date(c.Value, 'DD.MM.YYYY HH24:MI:SS') IS NOT NULL

doesn't work either.

Thanks!!

Goal to be able to do date BETWEEN queries on c.value in order to select date ranges.

Answer 1

The order that Oracle evaluates the conditions found in the where clause is not fixed. That is to say that it can choose to evaluate the condition containing TO_DATE before the other criteria, in which case the query will fail. To prevent that, add the ordered_predicates hint to your query, but be aware that this may require additional manual tuning to improve performance.

SELECT /*+ ordered_predicates */
               To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') somedate 
          FROM properties$aud a, 
               template_properties$aud b, 
               consumable_properties$aud c 
         WHERE Lower(a.name) = 'somedate' 
           AND a.id = b.property_id 
           AND b.id = c.template_property_id 
           AND To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') IS NOT NULL

---

Apparently ordered_predicates is deprecated starting with 10g. In that case, I think your only option is to use a sub-query in such a way that optimizer is forced to evaluate it first (i.e. it can't combine the queries). The easiest way to do this is to put rownum in the where statement of the inner query.

SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') somedate 
  FROM (SELECT value 
          FROM properties$aud a, 
               template_properties$aud b, 
               consumable_properties$aud c 
         WHERE Lower(a.name) = 'somedate' 
           AND a.id = b.property_id 
           AND b.id = c.template_property_id
           AND rownum > 0) 
 WHERE To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') IS NOT NULL