Barbara,
Shouldn't you be able to just subtract 36525 days from date fields in question? For example,
update advdb.custtest_barb
set crdate = crdate - 36525
where cusno=798489;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 -----------------------------------------------------------------
