Oracle表VARCHAR列多格式日期统一转换为单一格式（保留VARCHAR类型）

核心思路是先将不同格式的字符串转为DATE类型，再转成目标格式的VARCHAR，利用Oracle的日期转换函数处理格式识别，比直接字符串操作更可靠。以下是两种实用方案：

方案1：使用VALIDATE_CONVERSION（Oracle 12c+） #

适合已知所有日期格式的场景，通过VALIDATE_CONVERSION判断字符串匹配哪种格式，再统一转换。假设目标格式为'YYYY-MM-DD'：

SELECT 
  original_date_col,
  CASE
    -- 处理'dd-mon-yyyy'格式（指定英文月份缩写识别，避免会话语言干扰）
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH'), 'YYYY-MM-DD')
    -- 处理'dd-mm-yyyy'格式
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd-mm-yyyy') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd-mm-yyyy'), 'YYYY-MM-DD')
    -- 处理'dd/mm/yy'格式（用RR替代YY，智能识别世纪：05→2005，99→1999）
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd/mm/rr') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd/mm/rr'), 'YYYY-MM-DD')
    -- 无法转换的行返回原字符串或标记错误
    ELSE original_date_col -- 可替换为'INVALID_DATE'
  END AS unified_date_col
FROM your_table;

如果需要直接更新表中数据：

UPDATE your_table
SET original_date_col = CASE
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH'), 'YYYY-MM-DD')
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd-mm-yyyy') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd-mm-yyyy'), 'YYYY-MM-DD')
    WHEN VALIDATE_CONVERSION(original_date_col AS DATE, 'dd/mm/rr') = 1
      THEN TO_CHAR(TO_DATE(original_date_col, 'dd/mm/rr'), 'YYYY-MM-DD')
    ELSE original_date_col
  END;
COMMIT;

方案2：自定义转换函数（兼容低版本Oracle） #

如果是Oracle 12c以下版本，没有VALIDATE_CONVERSION，可以写自定义函数捕获转换异常，逐一尝试匹配格式：

CREATE OR REPLACE FUNCTION convert_to_unified_date(p_date_str VARCHAR2) RETURN VARCHAR2 IS
  v_date DATE;
BEGIN
  -- 尝试匹配'dd-mon-yyyy'
  BEGIN
    v_date := TO_DATE(p_date_str, 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH');
    RETURN TO_CHAR(v_date, 'YYYY-MM-DD');
  EXCEPTION WHEN OTHERS THEN NULL;
  END;
  
  -- 尝试匹配'dd-mm-yyyy'
  BEGIN
    v_date := TO_DATE(p_date_str, 'dd-mm-yyyy');
    RETURN TO_CHAR(v_date, 'YYYY-MM-DD');
  EXCEPTION WHEN OTHERS THEN NULL;
  END;
  
  -- 尝试匹配'dd/mm/rr'
  BEGIN
    v_date := TO_DATE(p_date_str, 'dd/mm/rr');
    RETURN TO_CHAR(v_date, 'YYYY-MM-DD');
  EXCEPTION WHEN OTHERS THEN NULL;
  END;
  
  -- 所有格式不匹配，返回原字符串
  RETURN p_date_str;
END;
/

使用函数查询：

SELECT original_date_col, convert_to_unified_date(original_date_col) AS unified_date_col
FROM your_table;

注意事项 #

• 月份缩写格式：务必指定NLS_DATE_LANGUAGE=ENGLISH，避免因会话语言不同导致的识别错误。
• 两位年份处理：优先用RR格式替代YY，它会根据当前年份智能判断世纪，更符合业务逻辑。

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