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.
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
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.
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
-------------------
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.
精彩评论