Literal does not match format string for Oracle SQL to_date in row column

Question

Dear SQL Guru from:

Environment: Oracle

I am trying to understand why I cannot make a choice to_date in a table column that contains rows. Z notes table with a name column The value in the example below contains a bunch of rows, some of which are in 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 | 

Next 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 | 

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 

Returns from:

ORA-01861: literal does not match format string

What am I missing here? Just fast:

  ... 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!!

The goal is to be able to set the date of BETWEEN requests to c.value to select date ranges.

Answer 1

The order in which Oracle evaluates the conditions found in the where clause is not fixed. That is, he can choose to evaluate the condition containing TO_DATE before other criteria, in which case the request will fail. To prevent this from happening, add the ordered_predicates hint to your query, but keep in mind that additional manual tuning may be required 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 deprecated starting at 10g. In this case, I think your only option is to use the subquery in such a way that the optimizer is forced to evaluate it first (i.e., it cannot combine the queries). The easiest way to do this is to put rownum in the where statement of the internal 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 

Answer 2

 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 ------------------- 

Answer 3

Another way is to convert to CASE. for instance

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

It gets REALLY ugly when sentences are complex, though.

Answer 4

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 will not work, you will need to check in another way. You can use regex (I think not used them at that time). Even better, if your data model allows you to distinguish between the rows in question.