sql - 文字与字符串列上的 Oracle SQL to_date 的格式字符串不匹配

Question

来自 Stack Overflow 的亲爱的 SQL 专家:

环境:甲骨文

我试图理解为什么我不能对包含字符串的表列进行 to_date 选择。注意在下面的示例中列名为 Value 的 tableZ 包含一堆字符串，其中一些是正确的格式，例如 6/20/2010 00:00:00。

表Z

| Value              |
| __________________ |
| 6/21/2010 00:00:00 |
| Somestring         |
| Some Other strings |
| 6/21/2010 00:00:00 |
| 6/22/2010 00:00:00 |

以下作品

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

这将返回类似(这很好):

| somedate            |
| __________________  |
| 21.06.2010 00:00:00 |
| 21.06.2010 00:00:00 |
| 22.06.2010 00:00:00 |

以下不起作用

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

回来了:

ORA-01861: 文字与格式字符串不匹配

我在这里缺少什么？只是一个快速说明:

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

也不起作用。

谢谢!!

目标能够对 c.value 进行日期 BETWEEN 查询以选择日期范围。

Answer 1

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

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