You could just subtract 36525 and see if that works. Do it in a
query first to make sure it 's the right number. If you keep to whole
numbers the time won 't change.
SQL > select to_char(sysdate-36525, 'MM-DD-YYYY hh24:mi:ss ') from dual;
> 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
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
--
E-mail correspondence to and from this address may be subject to
the North Carolina Public Records Law "NCGS.Ch.132 " and may be
disclosed to third parties by an authorized state official.
--
