If-else case in SQL*Loader

Question

I need to insert NULL into the table whenever the SQL*Loader encounters a bad date (0000-00-00 00:00:00) while reading a .csv file.

The bad date provided in the csv is always 0000-00-00 00:00:00.

I need help figuring out the case for it that handles both a bad date and a correct date.

There are two cases:

1. Date in the correct format (YYYY/MM/DD hh24:mi:ss)
2. Date in the bad format (0000-00-00 00:00:00)

Initially I had a simple statement in SQL control file as below, now I need to add the case to handle bad date as well.

START_DATE DATE "YYYY/MM/DD hh24:mi:ss" NULLIF (START_DATE = "NULL").

I need an SQL*Loader statement that handles both the above cases.

Answer 1

All data in a file is essentially a string until validated as a different datatype. So, you can treat it as a string for the bad data before converting it into a date:

START_DATE "TO_DATE(NULLIF(:START_DATE, '0000-00-00 00:00:00'), 'yyyy/mm/dd hh24:mi:ss')"

I'm a little concerned that the format of the "bad" date is different to that of the good date. It indicates something is going on that you're maybe not aware of for instance the data coming from two different systems or this field not being stored as a date in the source system.

If possible, I'd double check how this data is being constructed and what the intended meaning of the bad date is.