While loading the csv file into table using control file, am facing some date format issue. can anyone help me on this?

Question

LOAD DATA
        REPLACE
        INTO TABLE Tab_A
        Fields terminated by "," OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
        ITEM "trim(:ITEM)",
        LOC "trim(:LOC)",
        DATE "TO_DATE(:DATE,'DD-MM-YYYY')",
        DUR,
        QTY

When i execute the ctl file, am facing the below issue.

Record 10001: Rejected - Error on table tab_T, column DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Answer 1

You have bad data in your date column, which must be coming from a VARCHAR column and thus could contain anything. You'll need to run some queries to find it (also known as sanity checks). Look for data that does not match the expected formats or values.

Correction! You have a bad date format in the source data you are loading. To find it, maybe load it into a staging table where the date column is a VARCHAR2(10) (load it straight in), then run a regex against it to find values that don't match the format:

select item, loc, date
from Tab_A_stg
where not regexp_like(date, '^(0?[1-9]|[12][0-9]|3[01])-(0?[1-9]|1[012])-(19|20)\d\d$');

This will find rows where the date does not match a standard DD-MM-YYYY date.