I am facing very weird issue where I am loading a CSV file to Oracle External Table and then to STAGING table
There are two CSV files same Column structure for FEB and March Month.
I have a date column say "Requestdate" the Format in CSV file is in "MM/DD/YYYY"
I defined this column as VERCHAR in EXTERNAL TABLE DDL. in STAGING table as well the Column data type is VARCHAR
WHEN LOADING these 2 files (one for FEB-18 and other for Mar-18) in EXTERNAL TABLE and then to STAGING table
The DATE format for both these Files are appeared different after load:
I am unable to find out the root cause as in SOURCE CSV files both the Files are having same Formatting i.e."MM/DD/YYYY" for "REquestDateColumn" and in External Table and Staging table This column is defined as VARCHAR.
File DATE RequestDATE
2/5/2018 7:04 9/3/2017
2/5/2018 7:04 8/8/2014
2/5/2018 7:04 3/31/2017
2/5/2018 7:04 1/31/2017 -------------------> Note After Loaded Data is in MM/DD/YYYY for Feb Month File
3/5/2018 7:08 2017-10-11
3/5/2018 7:08 2018-01-16
3/5/2018 7:08 2017-10-10
3/5/2018 7:08 2018-03-17 ------------------> However it is loaded with YYYY-MM-DD Format for Mar Month in Staging.
Because of this weird behavior , I can't even define this column as DATE in External Table DDl, as for one Month the Format needs to be declared as MM/DD/YYYY and for Other Month is Should be defined as " YYYY-MM-DD"