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

一尘不染

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

sql

尊敬的来自堆栈溢出的SQL专家：

环境： Oracle

我试图了解为什么我不能在包含字符串的表列上进行to_date选择。注意在下面的示例中，tableZ的名称为Value的列包含一串字符串，其中一些是正确的格式，例如6/20/201000: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 |

以下作品

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进行查询之间日期的 *目标，以便选择日期范围。

阅读 262

2021-05-23

共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

2021-05-23