数字转日期报错‘day of month must be between 1 and last day of month’排查

编写SQL代码识别错误数据时遇到报错。数据中的日期列是数字类型，需要转换为日期格式（例如10322→01.03.2022），代码如下：

select *
  from (select *****_date,
               *****_hold_no,
               case
                 when length(*****_end_date) = 5 then
                  to_date(lpad(*****_end_date, 6, '0'), 'ddmmyy')
                 else
                  to_date(*****_end_date, 'ddmmyy')
               end as *****_end_date,
               case
                 when length(*****_start_date) = 5 then
                  to_date(lpad(*****_start_date, 6, '0'), 'ddmmyy')
                 else
                  to_date(*****_start_date, 'ddmmyy')
               end as *****_start_date
          from ods_****.*****
         where *****_KW_GLAC = ' '
           and *****_KW_CAAC is not null) A
 where *****_end_date < *****_start_date

执行时报错：day of month must be between 1 and last day of month，但将查询条件改为*****_end_date > *****_start_date后，查询可正常执行无报错，请问问题原因是什么？

核心问题是原始数据里存在非法日期数字，且这些非法值恰好出现在满足*****_end_date < *****_start_date的行中，具体拆解：

• 你的转换逻辑是把数字按ddmmyy格式转成日期，但部分原始数字本身是无效的日期组合——比如像320122（对应32日01月2022），这种数字在调用to_date时，会直接触发“日期超出当月有效范围”的报错。
• 数据库默认执行逻辑是先完成子查询中所有行的日期转换，再应用外层WHERE过滤。但当你把条件换成*****_end_date > *****_start_date时，数据库优化器可能做了谓词下推：把外层过滤条件提前到子查询阶段，先过滤掉不满足条件的行，再对剩下的行做日期转换。而那些包含非法日期的行恰好不满足>的条件，所以不会被执行转换，自然不会报错。
• 当用*****_end_date < *****_start_date时，这些非法日期的行满足过滤条件，必须被转换，转换过程中因日期无效直接抛出错误。

说白了就是：你要查的“结束日期早于开始日期”的错误数据里，混了一批连日期格式都不合法的垃圾数据，转换时直接触发了报错；反过来查的时候，这些垃圾数据不在结果范围内，所以没触发报错。

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