the default date format is dd-mon-yy
oracle always returns the date in this format, at first u'll hv to fetch the
date like this and then convert to whatever format u want to .
<merex_at_my-deja.com> wrote in message news:7unq2a$h7h$1_at_nnrp1.deja.com...
> I have a table with a varchar2 field, vc_dte, containing a date. I
> added a new column, dte, with a DATE datatype to the table and I want to
> update the new DATE column. The problem is that some of the records,
> when converted to dates, are invalid.
> vc_dte
> ------
> 980101
> 981205
> ...
>
> I have tried something like this:
>
> update m set dte = to_date((substr(vc_dte, 0, 2) || '/' ||
> substr(vc_dte, 3, 2) || '/' || substr(vc_dte, 5, 2)), 'yy/mm/dd')
>
> I get the following error, and no rows are updated:
> ORA-01858 a non-numeric character was found where a numeric was expected
>
> Is there something I can set which says, "Update the records where there
> are no errors, and skip the ones which have errors"
>
> Will I have to write a stored proc?
>
> Thanks,
> Ross Cormier
>
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 22 1999 - 14:57:20 CDT
