Re: update year in date column

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 23 Mar 2004 14:56:39 -0700
Message-Id: <6.0.0.22.2.20040323145550.030d5390@pop.centrexcc.com>

Just subtract 1200 months from the dates:

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select add_months(to_date('2099-03-31 15:24:58','yyyy-mm-dd hh24:mi:ss'),-1200) from dual;

ADD_MONTHS(TO_DATE(

1999-03-31 15:24:58

At 01:00 PM 3/23/2004, you wrote:

>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
>-----------------------------------------------------------------

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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:21:37 CST