Re: date mask in SQL*Loader

On Feb 9, 8:17 am, web guy <web-guy01XXSPAM..._at_comcast.net> wrote:
> I'm using SQL*Loader with a delimited file from Excel. I have a date
> field that the users are 'supposed' to populate with a format of
> 'mmddyyyy', and I simply do a to_date(:date_field,'MMDDRRRR') in the lod
> script and all is well.
>
> However, very often I get dates in the file with a format of 'M/D/YYYY'
> or 'MM/DD/YYYY', and rather then reject that record during load, I would
> of course like to load it anyway.
>
> Any suggestions on how to do this?
>
> Tim

Tim,
Not sure if the version you use supports the CASE statement, but if it does, try this expression for your column:

"case
  when instr(:yourDateColumn, '/') > 0 then to_date(:yourDateColumn, 'MM/DD/YYYY')
  else to_date(:yourDateColumn, 'MMDDRRRR') end"

it has been a while since I worked with SQL*Loader, but Received on Fri Feb 09 2007 - 12:35:28 CST