How to change date value into EXTERNAL TABLE from a csv file

Question

I need to populate my EXTERNAL TABLE from a .csv file received as input. Within my .csv file I have 3 fields which are respectively:

• ORDER_DATE -START -END Which are with the following format:
• December 4, 2023
• December 4, 2023 8:00:46 PM
• December 4, 2023 9:00:46 PM

What I want is to find a way to populate the data within my external table with the following format:

• 4/12/2023
• 4/12/2023 08:00
• 4/12/2023 09:00

How can I do this?

this is how I created my EXTERNAL_TABLE:

    CREATE TABLE EXT_TABLE 
   (
    "JOB" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
    "ORDER_DATE" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
    "START" VARCHAR2(255 BYTE) COLLATE "USING_NLS_COMP", 
    "END" VARCHAR2(255 BYTE) 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 'YYYYMMDDHH24MISS' DATE_FORMAT TIMESTAMP MASK 'YYYYMMDDHH24MISSFF6' NOTRIM MISSING FIELD VALUES ARE NULL   
  )
      LOCATION
       ( 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frn2gep9qi8i/INPUT/myfile.csv'
       )
    )
   REJECT LIMIT 25500000 
  PARALLEL ; 

Answer 1

I'd use strings (i.e. what you already did, varchar2 datatype). It'll allow all values to be part of external table. I presume it is just an intermediate step between a CSV file and target table.

In that case, you'd have all those values in external table, but - when transferring them into date datatype column in target table, use to_date function, e.g.

SQL> alter session set nls_date_Format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> select
  2    to_date('4/12/2023'      , 'dd.mm.yyyy hh24:mi') val1,
  3    to_date('4/12/2023 08:00', 'dd.mm.yyyy hh24:mi') val2
  4  from dual;

VAL1             VAL2
---------------- ----------------
04.12.2023 00:00 04.12.2023 08:00

SQL>

Both "dates" (with and without time component) are correctly modified into valid date datatype values.