loading date field using SQL*LOADER

From: Gordon T. Wu <wutao19_at_yahoo.com>
Date: Tue, 7 Oct 2003 09:52:50 -0400
Message-ID: <Jyzgb.249$Er.30968@mencken.net.nih.gov>

Hi group,

I'm loading data from plain text file into oracle. The text file has a birthdate field in the format of "yyyymmdd". In my script I have:

BIRTH_DATE position(27:34) date 'yyyymmdd'

Which works fine except the client denote the unkown part of the date by using the number 9. So if they only know someone's birth year and month (say, march 1977), but do not know the date, the value of the birthdate would be "19770399" SQL*Loader failes at this point because 1977-03-99 is not a valid date.

Can anyboday tell me how to deal with this situation?

Thanks a lot

Gordon Received on Tue Oct 07 2003 - 08:52:50 CDT