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.

Allan · Accepted Answer · 2010-06-24T19:17:51.747

10

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

edited Jun 24 '10 at 19:17

answered Jun 23 '10 at 20:20

Allan

17,141
4
52
69

A neat idea but I didn't get it to work with initial testing? – Rio Jun 23 '10 at 20:30
@Rio, if you want more help, saying "I didn't get it to work" is not good enough. – Jeffrey Kemp Jun 24 '10 at 01:38
His comment was in response to just the ordered_predicate section. The rest was added when I researched why it wasn't working... – Allan Jun 24 '10 at 02:31
Thank you Allan. This answer is valuable and hopefully will enlighten others as well! – Rio Jun 24 '10 at 13:59
As I understand it, the Oracle optimizer will sometimes push predicates into the in-line view, resulting in to_date and to_number firing on invalid values, even when the in-line view should filter them out. This is unfortunate, in cases like this example. See http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:11504677087008 and http://www.dbdebunk.com/page/page/1351381.htm – Shannon Severance Jun 24 '10 at 17:45
@Shannon Severance, that is correct. However, you can prevent oracle from pushing the predicate to the in-line view by adding a value to that view that would be changed if the predicate is changed. That's the purpose of including rownum in the inner query. – Allan Jun 24 '10 at 19:10

score 4 · Answer 2 · answered Jun 24 '10 at 00:28

4

Another technique is embed the conversion in a CASE. For example

SELECT * FROM table
WHERE col_a = '1'
AND case when col_a = '1' then to_date(col_b,'DD/MM/YYYY') end = trunc(sysdate)

This gets REALLY ugly fast when the clauses are complicated though.

answered Jun 24 '10 at 00:28

Gary Myers

34,963
3
49
74

score 2 · Answer 3 · answered Jun 23 '10 at 22:46

create or replace function to_date_or_null(v_str_date in varchar2
        , v_str_fmt in varchar2 default null) return date as
begin
    if v_str_fmt is null then
        return to_date(v_str_date);
    else
        return to_date(v_str_date, v_str_fmt);
    end if;
exception
    when others then
        return null;
end to_date_or_null;
/

Testing:

SQL> select to_date_or_null('2000-01-01', 'YYYY-MM-DD') from dual -- Valid;

TO_DATE_OR_NULL('20
-------------------
2000-01-01 00:00:00

SQL> select to_date_or_null('Not a date at all') from dual -- Not Valid;

TO_DATE_OR_NULL('NO
-------------------


SQL> select to_date_or_null('2000-01-01') from dual -- Valid matches my NLS settings;

TO_DATE_OR_NULL('20
-------------------
2000-01-01 00:00:00

SQL> select to_date_or_null('01-Jan-00') from dual -- Does not match my NLS settings;

TO_DATE_OR_NULL('01
-------------------

score 0 · Answer 4 · answered Jun 23 '10 at 20:02

0

Do you want to check if c.value is a valid format with

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

? This wont work, you will need to perform the check in some other way. You could use a regular expression (i guess, not used them in a while). Better yet if your data model would allow you to discern the rows in question.

answered Jun 23 '10 at 20:02

bert

7,566
3
31
47

No, my goal as stated above is to do BETWEEN queries so that I can select date ranges. The IS NOT NULL is just a simple way for me to check if the query works at all, and the oracle error suggests otherwise. – Rio Jun 23 '10 at 20:05

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

4 Answers4

Linked