Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: loading date field using SQL*LOADER
"FC" <flavio_at_tin.it> wrote:
>
>"Gordon T. Wu" <wutao19_at_yahoo.com> wrote in message
>news:Jyzgb.249$Er.30968_at_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
>>
>>
>
>BIRTH_DATE POSITION(27:34) DATE 'YYYYMMDD' "CONCAT(SUBSTR(:BIRTH_DATE, 1,
>4), CONCAT(REPLACE(SUBSTR(:BIRTH_DATE, 5, 2), '99', '01'),
>REPLACE(SUBSTR(:BIRTH_DATE, 7, 2), '99', '01')))"
>
>This handles the case of 99 for an unspecified month too.
>
>Bye,
>Flavio
>
Is the anyway to stop the client from doing something so stupid?
( Right!! - get a user to change behavior....that's like herding cats..) Received on Tue Oct 07 2003 - 12:09:12 CDT