Re: SQL Update and Invalid Data

For me, this error usually means that one or more of the date fields being converted does not have two digit month/days,

i.e., 8/21/99 instead of 08/21/99

Alan Shein <alanshein_at_erols.com> wrote in message news:7unreh$4sa$1_at_autumn.news.rcn.net...
> Try
>
> UPDATE m SET dte = to_date(vc_dte, 'yymmdd');
>
> When you want to select these new dates for viewing, use
>
> select to_char(dte, 'yy/mm/dd') from m;
>
> >>
> 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')
> <<
>
>
Received on Thu Oct 21 1999 - 17:28:30 CDT