Re: loading date field using SQL*LOADER

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:cns5ov0og0sdido53o8tj8mo6u831fvkn0_at_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..)
>

Well, the solution was not meant to be elegant. Soon I was tempted to patronize the submitter too, then I thought that sometimes people are given tasks and they simply don't have the option of changing the crappy thing on the other side of the cable, because, for instance, the program has been written in assembly on a platform and the source has been lost since ages and the only one who knows how to do it is now 85 years old.
Don't think this is so uncommon, I could mention several cases of manufacturing companies where the motto is "don't touch anything that works" and they are still running pdp-11 or other "iron-age" computers.

Bye,
Flavio Received on Tue Oct 07 2003 - 14:04:29 CDT