SQL Update and Invalid Data

From: <merex_at_my-deja.com>
Date: Thu, 21 Oct 1999 19:37:46 GMT
Message-ID: <7unq2a$h7h$1@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 Thu Oct 21 1999 - 14:37:46 CDT