Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: loading date field using SQL*LOADER

Re: loading date field using SQL*LOADER

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Tue, 07 Oct 2003 12:09:12 -0500
Message-ID: <cns5ov0og0sdido53o8tj8mo6u831fvkn0@4ax.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US