Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: loading date field using SQL*LOADER
"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
Received on Tue Oct 07 2003 - 11:37:43 CDT