文字与字符串列上的 Oracle SQL to_date 格式字符串不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3105003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Literal does not match format string for Oracle SQL to_date on a string column
提问by Rio
Dear SQL Gurus from Stack Overflow:
来自 Stack Overflow 的亲爱的 SQL 专家：
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.
我试图理解为什么我不能对包含字符串的表列进行 to_date 选择。请注意，在下面的示例中，列名为 Value 的 tableZ 包含一堆字符串，其中一些是正确的格式，例如 6/20/2010 00:00:00。
tableZ
表Z
| 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
ORA-01861: 文字与格式字符串不匹配
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!!
谢谢！！
Goalto be able to do date BETWEEN queries on c.value in order to select date ranges.
目标是能够对 c.value 进行日期 BETWEEN 查询以选择日期范围。
回答by Allan
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.
Oracle 评估 where 子句中找到的条件的顺序不是固定的。也就是说，它可以选择在其他条件之前评估包含 TO_DATE 的条件，在这种情况下查询将失败。为防止出现这种情况，请将ordered_predicates 提示添加到您的查询中，但请注意，这可能需要额外的手动调整以提高性能。
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_predicatesis 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 rownumin the where statement of the inner query.
显然ordered_predicates从 10g 开始不推荐使用。在这种情况下，我认为您唯一的选择是以优化器被迫首先评估它的方式使用子查询（即它不能组合查询）。最简单的方法是放入rownum内部查询的 where 语句。
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
回答by Shannon Severance
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
-------------------
回答by Gary Myers
Another technique is embed the conversion in a CASE. For example
另一种技术是将转换嵌入到 CASE 中。例如
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.
但是，当子句很复杂时，这会变得非常难看。
回答by bert
Do you want to check if c.value is a valid format with
你想检查 c.value 是否是一个有效的格式
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.
? 这行不通，您需要以其他方式执行检查。您可以使用正则表达式（我想，暂时没有使用它们）。如果您的数据模型允许您识别有问题的行，那就更好了。
