Loading invalid dates using SQL Loader

I was wondering if anyone knows a way to detect an invalid date when using SQLLoader.

I'm loading data in from a flat file. The date comes to me in the following format 19980228. Since the source system that generates this data doesn't check for a valid date I run into invalid dates when loading (19980230, 19981575, etc..) The majority (98%) are dates with incorrect last days of the month.

Currently the rows that have invalid dates are being loaded into a bad file. I then edit the bad file and update the date then I load the bad rows. This takes time and is a manual process.

I know of the nullif and defaultif functions but I don't think they can help me.

Here is what I would like to accomplish using SQLloader.

If a date has a incorrect last day of the month (19980230) make the date the last day of that month and year.
(19980228)

If a date has a incorrect month or year make the date the first day of that year.
(19??0131) or (20??0131)

In reviewing the Oracle documentation on SQLloader this seems impossible BUT I thought I would ask the Oracle Experts before writing PL/SQL code to check for valid dates before I use SQL loader.

Russ
imrad_at_icubed.com

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 22 1998 - 12:05:06 CST