Oracle中TO_DATE在WHERE子句抛ORA-01843但SELECT中正常的问题

你的猜测是否正确？ #

完全正确。这个问题的核心就是Oracle执行计划的执行顺序与SQL语句的书写顺序不一致。

尽管你在子查询里写了ABWABTNR <> 'PASRZ'来过滤那两条有问题的记录，但Oracle优化器为了提升执行效率，可能调整了执行步骤：它会先尝试对所有记录（包括那两条ABWABTNR='PASRZ'的）执行WHERE子句中的TO_DATE转换操作，之后再应用ABWABTNR <> 'PASRZ'的过滤条件。这就导致那两条值为'99999999999999'的记录在转换时触发ORA-01843错误，而它们本该被过滤掉。

而当你移除WHERE子句时，子查询的过滤逻辑先生效，那两条问题记录已经被排除，SELECT子句的转换操作只作用于合法数据，因此不会报错。

解决方案 #

针对这个问题，有几种可靠的修改方式，核心思路都是确保在执行日期转换前，先过滤掉所有无法转换的异常记录：

方案1：在子查询中提前验证日期合法性 #

通过正则表达式验证日期格式，在子查询中同时过滤ABWABTNR和非法日期字符串，确保进入后续逻辑的都是可转换的合法数据：

SELECT TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (
    SELECT ABWENDDAT, ABWBEGDAT
    FROM babw
    WHERE ABWABTNR <> 'PASRZ'
      -- 验证前8位是合法的YYYYMMDD格式
      AND REGEXP_LIKE(SUBSTR(ABWENDDAT, 1, 8), '^\d{4}(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])$')
      AND REGEXP_LIKE(SUBSTR(ABWBEGDAT, 1, 8), '^\d{4}(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])$')
) a
WHERE TRUNC(SYSDATE) BETWEEN TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                         AND TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD')

方案2：使用VALIDATE_CONVERSION函数（Oracle 12c+） #

Oracle 12c及以上版本提供了VALIDATE_CONVERSION函数，可以安全判断字符串是否能转换为指定类型，避免转换错误：

SELECT TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (
    SELECT ABWENDDAT, ABWBEGDAT
    FROM babw
    WHERE ABWABTNR <> 'PASRZ'
      AND VALIDATE_CONVERSION(SUBSTR(ABWENDDAT, 1, 8) AS DATE, 'YYYYMMDD') = 1
      AND VALIDATE_CONVERSION(SUBSTR(ABWBEGDAT, 1, 8) AS DATE, 'YYYYMMDD') = 1
) a
WHERE TRUNC(SYSDATE) BETWEEN TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                         AND TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD')

方案3：在WHERE子句中用CASE表达式规避错误 #

如果无法修改子查询，也可以在WHERE子句中通过CASE表达式，只对合法字符串执行转换：

SELECT TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
WHERE TRUNC(SYSDATE) BETWEEN 
    CASE 
        WHEN VALIDATE_CONVERSION(SUBSTR(a.ABWBEGDAT, 1, 8) AS DATE, 'YYYYMMDD') = 1 
        THEN TO_DATE(SUBSTR(a.ABWBEGDAT, 1, 8), 'YYYYMMDD') 
    END
    AND 
    CASE 
        WHEN VALIDATE_CONVERSION(SUBSTR(a.ABWENDDAT, 1, 8) AS DATE, 'YYYYMMDD') = 1 
        THEN TO_DATE(SUBSTR(a.ABWENDDAT, 1, 8), 'YYYYMMDD') 
    END

额外建议 #

从长远来看，建议将ABWENDDAT和ABWBEGDAT字段的数据类型改为DATE或TIMESTAMP，从根源上避免字符串转日期的各种问题，同时提升查询效率。

内容的提问来源于stack exchange，提问作者Bado