Re: SQL Loading Dates

<dennishancy_at_eaton.com> wrote in message news:8sf7rd$j2j$1_at_nnrp1.deja.com...
> I have a table which includes two date fields. I need to load some data
> into this table from a CSV file.
>
> The dates are in the format DD-MON-YY.
>
> In my CTL file, I specify each of the fields, identifying the date
> fields with a datatype of DATE.
>
> When I run the CTL file through SQL Loader, it results in error on each
> record where the second date field is not null. It gives no indication
> of errors on the first date field.
>
> The error I receive is as follows:
>
> ORA-01847: day of month must be between 1 and last day of month.
>
> Again, this is only showing an error for the second date field; it likes
> the first one.
>
> The dates in those records are valid. I can't see any other stray
> characters.
>
> Any clue as to what the problem might be? Thanks.

Try defining them as character fields and then follow it with this:

"TO_DATE(:colname,'DD-MON-YY')"

Kinda like this (the '...'s are other columns in the ctl file):

...,
my_date char position(5:13) "TO_DATE(:colname,'DD-MON-YY')", ...,

-Matt Received on Tue Oct 17 2000 - 06:30:47 CEST