Oracle SQL Developer导入双格式日期列的统一处理方案咨询

我要导入一个CSV文件（大小1604294）到Oracle，表名为fD，其中TRANS_DATE_TRANS_TIME列存在两种日期格式：

• 格式1：MM/DD/YYYY HH24:MI
• 格式2：YYYY-MM-DD HH24:MI:SS

Oracle SQL Developer无法直接将该列导入为DATE类型，我的目标是统一转换成MM/DD/YYYY HH24:MI格式。我先把它导入成VARCHAR2类型，还写了一段复杂的查询，但听说不建议把日期数据存为VARCHAR2类型。想请教两个问题：

1. 有没有办法直接把这个列导入成DATE类型？
2. 如果必须先导入成VARCHAR2，正确的转换查询语句应该怎么写？

附上我之前写的查询：

with DT as(
select TRANS_DATE_TRANS_TIME from fD
)
select TRANS_DATE_TRANS_TIME,
replace(to_char(to_date(TRANS_DATE_TRANS_TIME, case 
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{1}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{1}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{2}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{1}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{2}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{1}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}') then 'MM/DD/yyyy HH24:MI'
end), 'MM/DD/yyyy HH24:MI'),'') as newT from DT;

一、直接导入为DATE类型的方法 #

利用Oracle SQL Developer导入向导的自定义转换功能，步骤如下：

1. 启动导入向导，选择目标CSV文件，进入「列定义」步骤。
2. 找到TRANS_DATE_TRANS_TIME列，将数据类型设置为DATE。
3. 点击该列的「转换」按钮，输入以下自定义转换表达式：

   CASE
     WHEN REGEXP_LIKE(:TRANS_DATE_TRANS_TIME, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN
       TO_DATE(:TRANS_DATE_TRANS_TIME, 'YYYY-MM-DD HH24:MI:SS')
     ELSE
       TO_DATE(:TRANS_DATE_TRANS_TIME, 'MM/DD/YYYY HH24:MI')
   END
   

4. 完成后续导入流程，即可直接将该列导入为DATE类型，无需先存储为VARCHAR2。

二、导入为VARCHAR2后的转换语句 #

如果已经将列导入为VARCHAR2类型，可以简化查询逻辑——Oracle的TO_DATE函数能自动识别MM/DD/YYYY HH24:MI格式中一位或两位的日/月，无需拆分多个正则条件。优化后的语句如下：

SELECT
  TRANS_DATE_TRANS_TIME,
  TO_CHAR(
    CASE
      -- 匹配YYYY-MM-DD HH24:MI:SS格式
      WHEN REGEXP_LIKE(TRANS_DATE_TRANS_TIME, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN
        TO_DATE(TRANS_DATE_TRANS_TIME, 'YYYY-MM-DD HH24:MI:SS')
      -- 匹配MM/DD/YYYY HH24:MI格式（兼容一位/两位的日、月）
      ELSE
        TO_DATE(TRANS_DATE_TRANS_TIME, 'MM/DD/YYYY HH24:MI')
    END,
    'MM/DD/YYYY HH24:MI'
  ) AS newT
FROM fD;

说明： #

• 仅通过正则区分两种格式的核心特征（横杠分隔的年在前格式、斜杠分隔的月在前格式）即可，无需针对日/月的位数拆分条件。
• 不需要额外的REPLACE函数，TO_CHAR可直接输出目标格式的字符串。

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