Posted by 'Jim' on June 13, 2001 at 08:10:18 EST:
In Reply to: Processing multiple date formats length for the same column. posted by 'Jim' on June 08, 2001 at 13:20:36 EST:
Well I have figured out some of 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 and one or more of them is usally zero.
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'
But I get an error that the date is not formated correctly. Which I understand as the 'DATE' is looking for a mask. I just need help with the sql code in the "quotes" to handle the various cases.
Any one?
: 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, or at least handle the leading non-existent zeros?