求助：修正DB2中DDMMYYYY格式日期为YYYYMMDD的SQL查询

针对你DB2中MDATE列存在的YYYYMMDD（如20070730）、DDMMYYYY（含7位如1012017、8位如31122019）混合格式问题，可以通过多格式尝试转换+容错函数的方式统一处理，以下是具体SQL实现：

SELECT
    MDATE,
    COALESCE(
        -- 优先尝试8位的YYYYMMDD格式
        CASE WHEN LENGTH(CHAR(MDATE)) = 8 THEN
            TRY_TO_DATE(CHAR(MDATE), 'YYYYMMDD')
        END,
        -- 8位格式尝试失败后，转用DDMMYYYY格式
        CASE WHEN LENGTH(CHAR(MDATE)) = 8 THEN
            TRY_TO_DATE(CHAR(MDATE), 'DDMMYYYY')
        END,
        -- 处理7位格式：前两位为日，第三位为月（补零为两位月）
        CASE WHEN LENGTH(CHAR(MDATE)) = 7 THEN
            TRY_TO_DATE(
                SUBSTR(CHAR(MDATE), 1, 2) || LPAD(SUBSTR(CHAR(MDATE), 3, 1), 2, '0') || SUBSTR(CHAR(MDATE), 4, 4),
                'DDMMYYYY'
            )
        END,
        -- 7位格式的另一种可能：第一位为日，第二三位为月（补零为两位日）
        CASE WHEN LENGTH(CHAR(MDATE)) = 7 THEN
            TRY_TO_DATE(
                LPAD(SUBSTR(CHAR(MDATE), 1, 1), 2, '0') || SUBSTR(CHAR(MDATE), 2, 2) || SUBSTR(CHAR(MDATE), 4, 4),
                'DDMMYYYY'
            )
        END
    ) AS STANDARD_DATE
FROM
    YOUR_TABLE;

逻辑说明 #

1. 容错转换：使用TRY_TO_DATE替代普通TO_DATE，转换失败时返回NULL而非报错，确保SQL不会因格式异常中断。
2. 8位数值处理：先按YYYYMMDD尝试转换（匹配正常格式），失败则切换为DDMMYYYY格式（匹配如31122019这类8位日年月格式）。
3. 7位数值处理：针对日/月未补零的情况，分两种场景尝试补零后转换：
   • 前两位是日、第三位是月（如1012017 → 补零为10012017 → 转换为2017-01-10）
   • 第一位是日、第二三位是月（如5122018 → 补零为05122018 → 转换为2018-12-05）
4. 结果兜底：通过COALESCE依次尝试所有可能的转换方式，返回第一个成功转换的标准日期；若所有方式都失败，STANDARD_DATE会返回NULL，可后续过滤或单独处理这类异常数据。

注意事项 #

• TRY_TO_DATE是DB2 11.1及以上版本支持的函数，若使用旧版本，需用CASE结合日期有效性判断（比如检查年份范围、月份1-12、日期对应月份的最大天数）来替代，逻辑会更繁琐。
• 建议先抽取部分异常数据测试转换结果，确保符合预期后再全量执行。

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