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: FC <flavio_at_tin.it>
Date: Tue, 07 Oct 2003 16:37:43 GMT
Message-ID: <rZBgb.180863$hE5.6097234@news1.tin.it>

"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

Original text of this message

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