How about: update custtest_barb set crdate = add_months(crdate, -12*1000) where cusno=798489; Marc. ----- Original Message ----- From: "Barbara Baker" <barbarabbaker@xxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 23, 2004 2:00 PM Subject: update year in date column > > 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 > ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------