Oracle中如何将多种字符格式日期转换为日期类型？

问题根源 #

你提供的CASE语句存在语法错误，导致逻辑判断失效：

• instr(created_date,' ' = 0) 写法错误，正确格式应为 instr(created_date, ' ') = 0（错误地将比较运算放入了INSTR的参数括号内）
• instr(created_date,' ' > 0) 同理，正确写法是 instr(created_date, ' ') > 0

这种语法错误在SELECT查询时可能因部分数据未触发错误分支暂时正常，但INSERT操作全量处理数据时就会触发ORA-01858错误。

修正后的解决方案 #

方案1：修正CASE语句逻辑 #

先修正语法错误，同时加入TRIM()处理字符串前后的空格，避免因前导/尾随空格导致转换失败：

CASE 
   WHEN INSTR(created_date, ' ') = 0 
   THEN TO_DATE(TRIM(created_date), 'mm/dd/yyyy')
   ELSE TO_DATE(TRIM(SUBSTR(created_date, 1, INSTR(created_date, ' '))), 'mm/dd/yyyy')
END formatted_date

方案2：使用正则表达式简化处理 #

利用正则表达式直接提取日期部分，无需分支判断，更简洁可靠：

-- 提取开头的日期片段，自动忽略后续时间内容
TO_DATE(TRIM(REGEXP_SUBSTR(created_date, '^\s*\d{1,2}/\d{1,2}/\d{4}')), 'mm/dd/yyyy') AS formatted_date

或通过正则替换去掉时间部分：

TO_DATE(TRIM(REGEXP_REPLACE(created_date, '\s+.*$', '')), 'mm/dd/yyyy') AS formatted_date

方案3：Oracle 12c+ 容错转换写法 #

如果你的Oracle版本是12c及以上，可以使用TO_DATE()的容错参数，无需手动分支处理：

TO_DATE(
  TRIM(REGEXP_REPLACE(created_date, '\s+.*$', '')), 
  'mm/dd/yyyy', 
  'DEFAULT NULL ON CONVERSION ERROR'
) AS formatted_date

异常数据排查建议 #

转换前可先查询所有不符合格式的异常数据，提前清理：

SELECT created_date 
FROM your_table
WHERE NOT REGEXP_LIKE(created_date, '^\s*\d{1,2}/\d{1,2}/\d{4}(\s+.*)?$');

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