Oracle数据库如何拆分校验8位数字格式日期(YYYYMMDD)

假设存储8位日期值的字段名为date_num，所属业务表名为your_table，可根据实际库表名替换。以下写法完全匹配提出的三项校验规则：

校验逻辑对应说明 #

• 年份校验：截取字段前4位，判断数值小于当前系统年份
• 月份校验：截取字段第5-6位，判断取值在01-12区间内
• 日期校验：截取字段最后2位，结合对应年月判断不超过当月实际最大天数，自动适配大小月、闰年2月的日期上限

可定位具体不合格项的SQL写法 #

该写法会单独返回每一项校验的通过状态，方便定位脏数据类型：

SELECT
  date_num,
  -- 年份校验：1=通过 0=不通过
  CASE WHEN SUBSTR(TO_CHAR(date_num), 1, 4) < TO_CHAR(SYSDATE, 'YYYY') THEN 1 ELSE 0 END AS year_check_res,
  -- 月份校验：1=通过 0=不通过
  CASE WHEN TO_NUMBER(SUBSTR(TO_CHAR(date_num), 5, 2)) BETWEEN 1 AND 12 THEN 1 ELSE 0 END AS month_check_res,
  -- 日期校验：1=通过 0=不通过
  CASE
    WHEN TO_NUMBER(SUBSTR(TO_CHAR(date_num), 5, 2)) NOT BETWEEN 1 AND 12 THEN 0
    WHEN TO_NUMBER(SUBSTR(TO_CHAR(date_num), 7, 2)) BETWEEN 1
      AND TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TO_CHAR(date_num), 1, 6), 'YYYYMM')), 'DD')) THEN 1
    ELSE 0
  END AS day_check_res,
  -- 整体校验：1=全部规则通过 0=存在不通过项
  CASE
    WHEN SUBSTR(TO_CHAR(date_num), 1, 4) < TO_CHAR(SYSDATE, 'YYYY')
      AND TO_NUMBER(SUBSTR(TO_CHAR(date_num), 5, 2)) BETWEEN 1 AND 12
      AND TO_NUMBER(SUBSTR(TO_CHAR(date_num), 7, 2)) BETWEEN 1
        AND TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TO_CHAR(date_num), 1, 6), 'YYYYMM')), 'DD'))
    THEN 1 ELSE 0
  END AS all_check_res
FROM your_table
-- 如需直接筛选全部符合规则的数据，放开下方WHERE条件即可
-- WHERE
--   SUBSTR(TO_CHAR(date_num), 1, 4) < TO_CHAR(SYSDATE, 'YYYY')
--   AND TO_NUMBER(SUBSTR(TO_CHAR(date_num), 5, 2)) BETWEEN 1 AND 12
--   AND TO_NUMBER(SUBSTR(TO_CHAR(date_num), 7, 2)) BETWEEN 1
--     AND TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TO_CHAR(date_num), 1, 6), 'YYYYMM')), 'DD'))
;

简洁版写法（Oracle 12c及以上版本支持） #

如果不需要单独定位不合格项，仅需判断整体是否符合规则，可以利用Oracle内置的日期转换容错特性简化代码：

SELECT
  date_num,
  CASE
    WHEN TO_DATE(TO_CHAR(date_num), 'YYYYMMDD' DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL
      AND TO_NUMBER(SUBSTR(TO_CHAR(date_num), 1, 4)) < TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
    THEN 1 ELSE 0
  END AS all_check_res
FROM your_table
;

说明：如果存储日期的字段本身是CHAR/VARCHAR2字符类型，而非数字类型，去掉语句中嵌套的TO_CHAR(date_num)，直接对字段做SUBSTR截取即可。

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