"Literal does not match format string" when comparing two date fields

Question

I have a weird behavior going on here, and I'm hoping someone can explain it to me.

I have two fields in a query. One is a numeric field that is converted to a date by taking to_date('01/01/1960', 'mm/dd/yyyy') + somethingorother. Another is a text field, which includes at least one non-date value, that is converted to a date with to_date(textField, 'mm/dd/rrrr'). If I run the query, it runs fine. However, if I enclose the query in select * from ( ) where field1 > field2, it blows up with an "ORA-01861: literal does not match format string" error. If I try excluding the known non-date value in the where clause of the subquery that pulls back the text field, it doesn't help.

I know this is almost impossible to figure out without code, but I'm wondering if anyone can explain to me why it works without the filter, but blows up when I add it. Thanks.

Answer 1

The general problem is that since SQL is a set-based language, Oracle is free to evaluate your predicates in any order it chooses. If you have a VARCAHR2 column that stores some date values and some non-date values, that means that Oracle is free to either evaluate the predicate that filters out all of the non-date values first or to evaluate the predicate that checks whether one of the converted DATE values is greater than the other first. If it happens to evaluate the DATE inequality predicate (field1 > field2) before filtering out the non-date values, you'll get an error.

The fact that SQL is set-based is one of the major reasons that using the wrong data type is so problematic-- you can't ever be certain that a query is always going to filter out the non-convertable data before calling the conversion function. Even if you set up abstraction barriers like views that filter out the invalid data, the optimizer is free to reorder predicates so you can easily find that your queries end up breaking your abstraction barriers or that you have queries that work most of the time unless the optimizer happens to pick a different execution plan. Jonathan Gennick has a very enjoyable article Subquery Madness that talks about this specific issue.

You can write your own conversion function that ignores the exceptions and use that in your query. For example, you can create a function

CREATE OR REPLACE FUNCTION my_to_date( p_date_str    IN VARCHAR2,
                                       p_format_mask IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_Str, p_format_mask ); 
  RETURN l_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;

and then use that function in your query

SELECT *
  FROM (SELECT to_date('01/01/1960', 'mm/dd/yyyy') + somethingorother field1,
               my_to_date( textField, 'mm/dd/rrrr' ) field2
          FROM your_table
         WHERE some_condition)
 WHERE field1 > field2

That will work because it will be valid to call my_to_date on any string whether or not it evaluates to a valid DATE so your query no longer depends on the order in which Oracle chooses to evaluate the predicates.

Answer 2

I'm guessing that one of the dates in your varchar2 column - notice the inconsistency - isn't in the format you indicate. Please don't store dates in a character column. Use a date column, it stops this from happening at all.

Create the following function:

create or replace function is_date
        ( Pdate varchar2
        , Pformatstring varchar2 ) return number is

   l_date date;

begin

   l_date := to_date(Pdate, Pformatstring);

   return 1;
-- raise an exception when you can-t convert to a date.
exception when others then
   return 0;

end;

Then run in on your table:

select <columns>
  from my_table
 where is_date(textfield,  'mm/dd/rrrr') = 0

This'll show you where your data is wrong.

As noted in the comments, it may seem to run okay initially as you're not selecting all the data.

---

There is one other alternative, though still related to storing dates as a character. If you're doing a complicated join, something like the following:

select a.a, a.b
  from my_table a
  join another_table b
    on to_date(replace(a.textfield,'?'),'mm/dd/rr') = b.a_date

Then Oracle doesn't necessarily evaluate everything in the order you intend. You'll have to change it to:

select x.*
  from ( select a, b, to_date(replace(a.textfield,'?'),'mm/dd/rr') as another_date
           from my_table ) x
  join another_table b
    on another_date = b.a_date