Re: SQL Question: How to do an UPDATE to partially change a value?

From: Hakan Eren <heren_at_home.com>
Date: 2000/02/16
Message-ID: <38AAE879.3DE65E8F@home.com>#1/1

Hi,

A sample run is in the following:

SQLWKS> create table temp
     2>  (date_field DATE)
     3> /

Statement processed.
SQLWKS> insert into temp values(to_date('20990323', 'YYYYMMDD'))

     2> /
1 row processed.
SQLWKS> commit
Statement processed.

SQLWKS> /
SQLWKS> select * from temp
     2> /
DATE_FIELD          
--------------------

03/23/2099 00:00:00
1 row selected.
SQLWKS> UPDATE temp

     2> SET date_field = TO_DATE('1999' || TO_CHAR(date_field, 'MMDDHH24MISS'), 'YYYYMMDDHH24MISS')

     3>   WHERE TO_CHAR(date_field, 'YYYY') = '2099'
     4> /

1 row processed.
SQLWKS> commit
Statement processed.

SQLWKS> /
SQLWKS> select * from temp
     2> /
DATE_FIELD          
--------------------

03/23/1999 00:00:00
1 row selected.

source:
create table temp
 (date_field DATE)
/
insert into temp values(to_date('20990323', 'YYYYMMDD')) /
commit
/
select * from temp
/
UPDATE temp
  SET date_field = TO_DATE('1999' || TO_CHAR(date_field, 'MMDDHH24MISS'), 'YYYYMMDDHH24MISS')
  WHERE TO_CHAR(date_field, 'YYYY') = '2099' /
commit
/
select * from temp
/

Hakan

robert.bowen_at_mubimedia.com wrote:
>
> Hello all. I have a question - I have a date column with screwed up
> data. Instead of '1999' in some rows I have '2099'. My own latent Y2K
> bug. Anyway ... what's the easiest way to change these rows, just to
> get rid of the '20' and replace it with '19' ?
>
> I tried doing it using wildcards and old.date_col but I can't get it to
> work ...
>
> Anyone got a quick fix? I need a hit!
>
> Thanks.
> syg
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Feb 16 2000 - 00:00:00 CST