Oracle数据库多格式字符串日期转DD-MMM-YYYY格式技术咨询

那个DECODE的思路方向是对的——通过字符特征判断日期格式再转换，但它的问题是覆盖的格式太少了，完全没处理你提到的14-Jun-2017、May 2017、February 1, 2017这些场景，直接用的话肯定还是会报你之前遇到的转换错误。

接下来针对你的需求，分两种情况给你更靠谱的方案：

如果你用的是Oracle 12c及以上版本（推荐） #

Oracle 12c引入了VALIDATE_CONVERSION函数，可以先判断字符串能否按指定格式转成日期，避免转换报错。结合CASE WHEN就能覆盖你所有的格式场景，还能处理缺日的情况（默认取当月第一天）：

SELECT 
  ExamDate,
  -- 先转成DATE类型，再格式化为DD-Mon-YYYY
  TO_CHAR(
    CASE
      -- 处理DD-Mon-YYYY格式（如14-Jun-2017）
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'DD-Mon-YYYY') = 1 
        THEN TO_DATE(ExamDate, 'DD-Mon-YYYY')
      -- 处理MM/DD/YYYY或MM/DD/YY（如9/15/2017）
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'MM/DD/YYYY') = 1 
        THEN TO_DATE(ExamDate, 'MM/DD/YYYY')
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'MM/DD/YY') = 1 
        THEN TO_DATE(ExamDate, 'MM/DD/YY')
      -- 处理带逗号的格式（如February 1, 2017）
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'Month DD, YYYY') = 1 
        THEN TO_DATE(ExamDate, 'Month DD, YYYY')
      -- 处理缺日的格式（如May 2017），默认取当月第一天
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'Month YYYY') = 1 
        THEN TRUNC(TO_DATE(ExamDate, 'Month YYYY'), 'MM')
      WHEN VALIDATE_CONVERSION(ExamDate AS DATE, 'Mon YYYY') = 1 
        THEN TRUNC(TO_DATE(ExamDate, 'Mon YYYY'), 'MM')
      -- 其他无法识别的格式或NULL，返回NULL
      ELSE NULL
    END, 'DD-Mon-YYYY'
  ) AS Formatted_ExamDate
FROM your_table;

如果你用的是Oracle 11g及以下版本 #

没有VALIDATE_CONVERSION的话，写一个自定义PL/SQL函数会更稳妥——通过逐个尝试格式并捕获转换错误的方式，避免整个查询崩溃：

首先创建转换函数：

CREATE OR REPLACE FUNCTION convert_to_date(p_date_str VARCHAR2) RETURN DATE IS
  v_date DATE;
BEGIN
  -- 先处理NULL值
  IF p_date_str IS NULL THEN
    RETURN NULL;
  END IF;

  -- 尝试DD-Mon-YYYY格式
  BEGIN
    v_date := TO_DATE(p_date_str, 'DD-Mon-YYYY');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 尝试MM/DD/YYYY格式
  BEGIN
    v_date := TO_DATE(p_date_str, 'MM/DD/YYYY');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 尝试MM/DD/YY格式
  BEGIN
    v_date := TO_DATE(p_date_str, 'MM/DD/YY');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 尝试Month DD, YYYY格式（如February 1, 2017）
  BEGIN
    v_date := TO_DATE(p_date_str, 'Month DD, YYYY');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 尝试Month YYYY格式（如May 2017），返回当月第一天
  BEGIN
    v_date := TRUNC(TO_DATE(p_date_str, 'Month YYYY'), 'MM');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 尝试Mon YYYY格式（如Jun 2017），返回当月第一天
  BEGIN
    v_date := TRUNC(TO_DATE(p_date_str, 'Mon YYYY'), 'MM');
    RETURN v_date;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  -- 所有格式都匹配失败，返回NULL
  RETURN NULL;
END;
/

然后在视图里调用这个函数，格式化输出：

SELECT 
  ExamDate,
  TO_CHAR(convert_to_date(ExamDate), 'DD-Mon-YYYY') AS Formatted_ExamDate
FROM your_table;

最后提醒 #

• 对于那些完全无法识别的垃圾数据，上面的方案会返回NULL，你可以根据需求改成标记值（比如'Invalid Date'），但要注意字段类型的一致性。
• 如果之后还有新的日期格式出现，只需要在CASE或者函数里新增对应的判断/尝试逻辑即可。

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