update year in date column 2004-03-23 - By Barbara Baker
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 (See ORA-00923.ora-code.com): 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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
