Re: SQL Update and Invalid Data

Your sql looks good to me. So I suggest that you do a check on the vc_dte field to identify the invalid dates before you do the update. (things like the 3 and 4 characters are not between '10' and '12', etc.) I think once you see what is wrong with the data in vc_dte, you can figure either a where clause to exclude them from the update or do a different update statement to fix the invalid dates. Good Luck!

In article <7unq2a$h7h$1_at_nnrp1.deja.com>, merex_at_my-deja.com wrote:

> 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.

TIA Received on Fri Oct 22 1999 - 19:28:26 CDT