update year in date column

  • From: Barbara Baker <barbarabbaker@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 23 Mar 2004 12:00:47 -0800 (PST)

OK, I'm just going to bite the bullet and claim total
ignorance here and see if someone takes pity on me :(
(sad face a nice touch, eh?)

Someone has accidently updated 378 records in the
customer table.  The date changed from 1999 to 2099.
I need to change just the year back to 1999 for these
records.  (I'd like to keep the time stamp, if
possible.  The month and day are correct.)  I looked
on metalink, google, and asktom.  Some nice examples,
but not what I really need.

Thought I'd be clever and subtract 1000 from the date.
This works, but I don't know how to get it formatted
back into a date.

I'd prefer just sqlplus, but will use pl/sql if
necessary.

Here's what I've done so far:
(solaris 9 oracle 9.2.0.4)

JServer Release 9.2.0.4.0 - Production

DOC>     CUSNO CUSNAME                            
CRDATE
DOC>---------- -----------------------------------
----------
DOC>    798489 GILBERT, ROSS                      
09/16/2099
DOC>    826744 HOEFLER, MATT                      
10/08/2099
DOC>    795126 FORT,  JOETTA                      
09/08/2099
DOC>*/

SQL> 
SQL> ---select to_date(to_char(crdate,'MM/DD/YYYY'))
from customer where cusno=798489;
SQL> ---update advdb.custtest_barb
SQL>  ---set crdate= to_char(crdate,'MM/DD/YYYY'))
SQL> ---from customer where cusno=798489;
SQL> 
SQL> select
to_date(to_char(crdate,'ddmmyyyy')-1000),'MM/DD/YYYY')
  2     from customer where cusno=798489;
select
to_date(to_char(crdate,'ddmmyyyy')-1000),'MM/DD/YYYY')
                                                      
     *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> 
SQL> 
SQL> --- these both work
SQL> select to_char(crdate,'ddmmyyyy')  from
custtest_barb where cusno=798489;

TO_CHAR(
--------
16092099

SQL> select to_char(crdate,'ddmmyyyy') - 1000 from
custtest_barb where cusno=798489;

TO_CHAR(CRDATE,'DDMMYYYY')-1000
-------------------------------
                       16091099

Thank for any assistance.

Barb



__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: