如何将CSV中的日期格式转换后导入Oracle外部表

1. 调整外部表定义，正确解析CSV日期格式 #

先把外部表的日期类字段从VARCHAR2改为原生日期/时间类型，并在访问参数中指定匹配CSV格式的掩码：

CREATE TABLE EXT_TABLE 
(
    "JOB" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
    "ORDER_DATE" DATE COLLATE "USING_NLS_COMP", 
    "START" TIMESTAMP COLLATE "USING_NLS_COMP", 
    "END" TIMESTAMP COLLATE "USING_NLS_COMP"
)  
DEFAULT COLLATION "USING_NLS_COMP" 
ORGANIZATION EXTERNAL 
( 
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY "DATA_PUMP_DIR"
    ACCESS PARAMETERS
    ( 
        RECORDS DELIMITED BY DETECTED NEWLINE 
        NOLOGFILE NOBADFILE NODISCARDFILE 
        READSIZE=10000000 
        CREDENTIAL 'DWH' 
        FIELDS TERMINATED BY ';' 
        OPTIONALLY ENCLOSED BY '"' 
        CONVERT_ERROR REJECT_RECORD 
        DATE_FORMAT DATE MASK 'Month dd, YYYY'  -- 匹配CSV中ORDER_DATE的英文月份格式
        DATE_FORMAT TIMESTAMP MASK 'Month dd, YYYY HH:MI:SS AM'  -- 匹配START/END的带12小时制时间格式
        NOTRIM 
        MISSING FIELD VALUES ARE NULL   
    )
    LOCATION
    ( 
        'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frn2gep9qi8i/INPUT/myfile.csv'
    )
)
REJECT LIMIT 25500000 
PARALLEL; 

2. 查询时格式化输出目标格式 #

通过TO_CHAR函数将原生日期/时间类型转换为你需要的字符串格式：

SELECT
    "JOB",
    TO_CHAR("ORDER_DATE", 'DD/MM/YYYY') AS ORDER_DATE,
    TO_CHAR("START", 'DD/MM/YYYY HH24:MI') AS START,
    TO_CHAR("END", 'DD/MM/YYYY HH24:MI') AS END
FROM EXT_TABLE;

补充说明 #

• 原生类型解析：把CSV字符串转成Oracle日期/时间类型，避免字符串处理的格式歧义，也方便后续的日期运算。
• 掩码匹配规则：'Month dd, YYYY'对应英文月份全称+日期+年份的格式；'Month dd, YYYY HH:MI:SS AM'对应带12小时制标记的时间格式。
• 输出格式控制：'DD/MM/YYYY'生成日/月/年格式，'HH24:MI'自动补前导零，生成24小时制的小时和分钟。

如果无法修改外部表字段类型，也可以直接对原VARCHAR2字段进行转换：

SELECT
    "JOB",
    TO_CHAR(TO_DATE("ORDER_DATE", 'Month dd, YYYY'), 'DD/MM/YYYY') AS ORDER_DATE,
    TO_CHAR(TO_TIMESTAMP("START", 'Month dd, YYYY HH:MI:SS AM'), 'DD/MM/YYYY HH24:MI') AS START,
    TO_CHAR(TO_TIMESTAMP("END", 'Month dd, YYYY HH:MI:SS AM'), 'DD/MM/YYYY HH24:MI') AS END
FROM EXT_TABLE;

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