Oracle SQL loader -to load inconsistent date formats in same column

Question

My client has supplied 2 feed files of two different year to load into our database.The problem is cloumn(TIME_STAMP) receives data in two different formats.

For year 2019: The TIME_STAMP is received in format MM/DD/YYYY HH24:MI:SS

For year 2020: The column TIME_STAMP is received in format YYYY/MM/DD HH24:MI:SS

I have written a code in.ctl file like

"to_date(:TIME_STAMP, case when regexp_substr(:TIME_STAMP,'\w+',1,2)=regexp_substr(:TIME_STAMP,'\w+',1,2) then 'YYYY/MM/DD HH24:MI:SS' else
'MM/DD/YYYY HH24:MI:SS' end)",

The problem is I am getting my 2019 file loaded but my 2020 giving

"ORA-01843:Not a valid month"

Answer 1

Consider:

to_date(
    :TIME_STAMP, 
    case 
        when substr(:TIME_STAMP, 1, 4) = '2020' then 'YYYY/MM/DD HH24:MI:SS' 
        else 'MM/DD/YYYY HH24:MI:SS' 
    end
)

The logic is tom simply check the first 4 characters of the string; if it is '2020', then we use the first sring format, else we use the other one.