Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: update year in date column

RE: update year in date column

From: Eberhard, Jeff <Jeff.Eberhard_at_TriumphGS.com>
Date: Tue, 23 Mar 2004 14:29:38 -0700
Message-ID: <86B94CDA99C77A408129A378D2DA8DB80BC7A2@compqsrv.rolls-roycegs.com>


Perhaps you could use the add_months function and subtract 1200 months from the date.

Select to_char(add_months(crdate,-1200),'ddmmyyyy') from custtest_barb where cusno=798489;

--Jeff

-----Original Message-----

From: Barbara Baker [mailto:barbarabbaker_at_yahoo.com] Sent: Tuesday, March 23, 2004 1:01 PM
To: oracle-l_at_freelists.org
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_at_freelists.org 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

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
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
Received on Tue Mar 23 2004 - 17:24:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US