导致此日期比较错误的原因是什么？

Question

我一直在努力理解为什么这个查询不能工作。此所有者的所有分区名称都具有格式为'R_DATE_20170831‘的值。但下面的查询始终返回错误ORA-01841 (完整)年份必须介于-4713和+9999之间，而不是0。当我单独显示每个日期部分时，它们都在该范围内。我的NLS_DATE_FORMAT显示了一个值'DD-MON-RR‘。

SELECT COUNT(*)
FROM dba_tab_partitions
WHERE table_owner = 'myId' AND
      TO_DATE(SUBSTR(partition_name,INSTR(partition_name,'_',1,2) + 1),'YYYYMMDD') < TRUNC(SYSDATE) - 10;

我还尝试在Sysdate-10周围放置一个带有适当掩码的TO_DATE，但仍然出现错误。有什么见解吗？谢谢!

Answer 1

我在SQL Server中看到过类似的情况，这是由于WHERE表达式不一定是按顺序计算的。这意味着其他分区正在经历日期算术逻辑--并导致失败。

CASE保证了计算的顺序。这行得通吗？

SELECT COUNT(*)
FROM dba_tab_partitions
WHERE table_owner = 'myId' AND
      (CASE WHEN  table_owner = 'myId'
            THEN TO_DATE(SUBSTR(partition_name, INSTR(partition_name, '_', 1, 2) + 1), 'YYYYMMDD')
       END) < TRUNC(SYSDATE) - 10;

或者这个版本：

SELECT COUNT(*)
FROM dba_tab_partitions
WHERE table_owner = 'myId' AND
      (CASE WHEN regexp_like(partition_name, '[^_]*_[^_*]_[0-9]{8}$'
            THEN TO_DATE(SUBSTR(partition_name, INSTR(partition_name, '_', 1, 2) + 1), 'YYYYMMDD')
       END) < TRUNC(SYSDATE) - 10;