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: Marc Perkowitz <mperkowitz_at_twjconsulting.com>
Date: Tue, 23 Mar 2004 15:28:37 -0600
Message-ID: <01d101c4111d$cdd35740$33099943@winbook>


How about:

update custtest_barb
  set crdate = add_months(crdate, -12*1000)   where cusno=798489;

Marc.

>
> 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 - 16:56:55 CST

Original text of this message

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