如何仅格式化不会抛出ORA-01843错误的Oracle数据库记录？

遇到这种混合格式的字符串列，直接批量转换肯定会因为不兼容的格式报错。我们可以通过先判断字符串是否符合目标日期格式，再选择性转换的方式来处理，下面分两种场景给出方案：

方案一：Oracle 12cR2及以上版本（推荐） #

从Oracle 12cR2开始，官方提供了VALIDATE_CONVERSION函数，可以直接检查字符串是否能按指定格式转换为日期，无需自定义函数，用起来非常方便。

示例SQL：

SELECT 
    CASE 
        WHEN VALIDATE_CONVERSION(TRIM(MyColumn) AS DATE, 'YYMMDD') = 1 
        THEN TO_CHAR(TO_DATE(TRIM(MyColumn), 'YYMMDD'), 'MM/DD/YYYY')
        ELSE MyColumn
    END AS FormattedDate
FROM MyTable;

简单解释：

• VALIDATE_CONVERSION(...) = 1意味着当前字符串可以按照YYMMDD格式成功转换为日期；
• 符合条件的记录会被转换成MM/DD/YYYY的易读格式，不符合的直接保留原字段值。

方案二：Oracle 12cR2以下版本 #

低版本没有VALIDATE_CONVERSION这个便捷函数，我们可以写一个自定义函数，在函数内部捕获转换异常，返回转换后的值或原字符串。

步骤1：创建自定义转换函数 #

CREATE OR REPLACE FUNCTION Convert_Date_If_Valid(p_str IN VARCHAR2) 
RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(TRIM(p_str), 'YYMMDD');
    RETURN TO_CHAR(v_date, 'MM/DD/YYYY');
EXCEPTION
    WHEN OTHERS THEN
        RETURN p_str;
END;
/

函数逻辑说明：

• 尝试将输入字符串按YYMMDD格式转换为日期，如果成功则返回格式化后的字符串；
• 如果转换失败（比如遇到AO0102、JAN 31这类不兼容格式），直接捕获异常并返回原字符串。

步骤2：调用函数查询 #

SELECT Convert_Date_If_Valid(MyColumn) AS FormattedDate
FROM MyTable;

额外扩展：支持多种有效日期格式 #

如果你的列里除了YYMMDD，还有其他可以转换的格式（比如MON DD类型的JAN 31），可以扩展判断逻辑。以12cR2+版本为例：

SELECT 
    CASE 
        WHEN VALIDATE_CONVERSION(TRIM(MyColumn) AS DATE, 'YYMMDD') = 1 
        THEN TO_CHAR(TO_DATE(TRIM(MyColumn), 'YYMMDD'), 'MM/DD/YYYY')
        WHEN VALIDATE_CONVERSION(TRIM(MyColumn) AS DATE, 'MON DD', 'NLS_DATE_LANGUAGE=ENGLISH') = 1
        THEN TO_CHAR(TO_DATE(TRIM(MyColumn), 'MON DD', 'NLS_DATE_LANGUAGE=ENGLISH'), 'MM/DD/YYYY')
        ELSE MyColumn
    END AS FormattedDate
FROM MyTable;

注意：处理MON DD格式时，一定要指定NLS_DATE_LANGUAGE=ENGLISH，避免因数据库语言设置不同导致的转换失败。

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