update year in date column 2004-03-23 - By Barbara Baker
Wow.
I had no idea there would be so many ways to do this.
Thank you all so very much for your quick responses.
I 've already executed Marc 's fix, and everybody is
happy.
Thanks!
Barb
--- Marc Perkowitz <mperkowitz@(protected) >
wrote:
> How about:
>
> update custtest_barb
> set crdate = add_months(crdate, -12*1000)
> where cusno=798489;
>
> Marc.
>
> -- -- Original Message -- --
> From: "Barbara Baker " <barbarabbaker@(protected) >
> To: <oracle-l@(protected) >
> 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 (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
> >
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
__ ____ ____ ____ ____ ____ ______
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
