从VARCHAR2字段提取各用户最近违规日期，解决ORA-01830报错

问题原因 #

ORA-01830报错的根因是VIOLATION_DATES字段存在用>拼接的多日期字符串，字符串长度超过了DD-MON-YY日期格式的匹配长度，导致日期转换失败。

解决方案 #

由于你没有修改表存储逻辑的权限，可先将每个字段中用>分隔的所有日期拆分出来，统一转换为日期类型后，再按用户ID分组取最大值即可，通用实现SQL如下：

SELECT 
    USER_ID,
    TO_CHAR(
        MAX(TO_DATE(TRIM(ONE_DATE), 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN')),
        'DD-MON-YY'
    ) AS MOST_RECENT_VIOLATIONS
FROM (
    -- 拆分多日期字段为单行单日期
    SELECT 
        t.USER_ID,
        REGEXP_SUBSTR(t.VIOLATION_DATES, '[^>]+', 1, LEVEL) AS ONE_DATE
    FROM 你的实际表名 t
    CONNECT BY LEVEL <= REGEXP_COUNT(t.VIOLATION_DATES, '>') + 1
        AND PRIOR t.USER_ID = t.USER_ID
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免多行拆分时产生笛卡尔积
)
WHERE ONE_DATE IS NOT NULL
GROUP BY USER_ID
ORDER BY USER_ID;

补充说明 #

1. 代码中加入了NLS_DATE_LANGUAGE = AMERICAN参数，避免数据库默认语言非英文时，Jul、Aug这类英文月份缩写转换报错
2. 如果你能确认所有多日期拼接的记录中，>后面的日期一定晚于前面的日期，也可以用更简洁的高性能写法，直接取每个字段最后一个>后面的日期即可：

SELECT 
    USER_ID,
    TO_CHAR(
        MAX(TO_DATE(TRIM(SUBSTR(VIOLATION_DATES, INSTR(VIOLATION_DATES, '>', -1) + 1)), 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN')),
        'DD-MON-YY'
    ) AS MOST_RECENT_VIOLATIONS
FROM 你的实际表名
GROUP BY USER_ID
ORDER BY USER_ID;

3. 注意替换SQL中的你的实际表名为你业务表的真实名称，同时确认字段名是否和代码中一致，你之前写的SQL中用了USR_ID，如果实际字段是USER_ID需要对应调整。

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