Oracle查询含TO_CHAR日期转换条件时出现ORA-01847错误求助

错误本质 #

ORA-01847是日期日部分无效的错误，核心问题是你的YMD字段中存在无法转换为有效日期的值（比如'20230230'、'20230431'这类现实中不存在的日期格式）。

你认为数据正常，是因为只检查了BETWEEN '20230726' AND '20230905'范围内的数据，但实际上表中超出该范围的行里藏着无效日期值。

加条件与不加条件的差异 #

Oracle的SQL执行计划不会严格按照你写的WHERE子句顺序执行：

• 不加星期几过滤条件时，优化器会优先执行YMD BETWEEN '20230726' AND '20230905'，只对这个范围内的有效日期做后续处理，自然不会触发转换错误；
• 加上TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd')相关条件后，优化器可能先对全表的YMD执行日期转换（用来计算星期几），这时碰到表中其他位置的无效日期，就直接触发了ORA-01847错误。

另外补充：你写的TO_CHAR(TO_CHAR(TO_DATE(...), 'd'))是多余的嵌套，TO_DATE转换后直接用TO_CHAR取星期几即可，不需要两层TO_CHAR。

解决办法 #

方法1：先过滤日期范围再计算星期几 #

把日期范围过滤放在子查询中，确保后续只处理有效日期：

SELECT TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') AS WEEK
     , OM.* 
FROM (
    SELECT * 
    FROM ORDER_MASTER 
    WHERE YMD BETWEEN '20230726' AND '20230905'
) OM
WHERE TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') <> '7'
  AND TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') <> '1'
ORDER BY YMD

方法2：用VALIDATE_CONVERSION过滤无效日期 #

如果表中确实存在大量无效日期，先筛选出能正常转换的行：

SELECT TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') AS WEEK
     , OM.* 
FROM ORDER_MASTER OM
WHERE YMD BETWEEN '20230726' AND '20230905'
  AND VALIDATE_CONVERSION(YMD AS DATE, 'YYYYMMDD') = 1
  AND TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') <> '7'
  AND TO_CHAR(TO_DATE(OM.YMD, 'YYYYMMDD'), 'd') <> '1'
ORDER BY YMD

方法3：简化计算逻辑，减少重复转换 #

把星期几的计算提前到子查询中，避免多次重复转换：

SELECT WEEK, OM.*
FROM (
    SELECT TO_CHAR(TO_DATE(YMD, 'YYYYMMDD'), 'd') AS WEEK
         , OM.*
    FROM ORDER_MASTER OM
    WHERE YMD BETWEEN '20230726' AND '20230905'
) OM
WHERE WEEK NOT IN ('1', '7')
ORDER BY YMD

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