Re: Help with SQLLDR - how to use a control file to process multiple date formats for the same column?

Thanks!

I have tried it.

In my control file I have code like this: ...
START_DATE1 "to_date((lpad(:START_DATE1,'6','0')), 'YYMMDD')" , STATUS,
END_DATE "to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')" , ...

But the bad thing is if one of the many date fields contain zeros that row is ejected - I have nearly 10 date fields in each row, and one or more of them is usally zero. The Above statements do work for the cases when none of the date cells are zero (about 4 out of 20 000 will pass)
I need to write a statement that will handle the null case and put the correct value in - help?

I have tried code like this:
END_DATE date "to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')" nullif END_DATE = '0'
and I have tried the suggestion of:
RETURN_DATE DATE "LPAD(TO_CHAR(:RETURN_DATE),6,'0')" But I get an error saying that the date is not formated correctly. Which I understand as the 'DATE' is looking for a mask. How can it also handle the NULL case.

Any one?

orahelp_at_rediffmail.com (Chirag) wrote in message news:<2706fcc3.0106120325.6c1ea63f_at_posting.google.com>...
> Hi,
>
> Pl. try following format in control file.
>
> RETURN_DATE DATE "LPAD(TO_CHAR(:RETURN_DATE),6,'0')"
>
> With regards,
>
> CHIRAG SHAH
> Oracle Administator
>
>
>
> jim_esti_at_hotmail.com (Jim) wrote in message news:<f0e3dc0b.0106080857.641b914e_at_posting.google.com>...
> > Hi,
> >
> > In my control file I process dates with
> > RETURN_DATE date "YYMMDD" NULLIF RETURN_DATE = '0'. Similar code
> > exists for the other date related columns.
> > This does the trick for about 200 of the 20000 records. The date is
> > some times 6 digits and sometimes 5, 4 or 3 digits. As the date could
> > be 000101 (January 01, 2000) - but the text file just contains the
> > number 101 (the extract from the old DOS system drops the leading
> > zeros).
> >
> > How can make the control file to process multiple date formats for the
> > same column?
Received on Sat Jul 21 2001 - 23:51:38 CEST