From oracle-l-bounce@freelists.org Tue Mar 23 17:21:37 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2NNLbJ11098 for ; Tue, 23 Mar 2004 17:21:37 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2NNLbo11092 for ; Tue, 23 Mar 2004 17:21:37 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 469B83923B6; Tue, 23 Mar 2004 17:45:26 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19323-54; Tue, 23 Mar 2004 17:45:26 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 16EDA39555A; Tue, 23 Mar 2004 17:35:09 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 23 Mar 2004 17:33:25 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4AF8339249D for ; Tue, 23 Mar 2004 17:21:53 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14317-36 for ; Tue, 23 Mar 2004 17:21:53 -0500 (EST) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A3EF3393A06 for ; Tue, 23 Mar 2004 16:46:47 -0500 (EST) Received: from eagle.centrexcc.com ([137.186.208.78]) by priv-edtnes51.telusplanet.net (InterMail vM.6.00.05.02 201-2115-109-103-20031105) with ESMTP id <20040323215350.UCGQ9171.priv-edtnes51.telusplanet.net@eagle.centrexcc.com> for ; Tue, 23 Mar 2004 14:53:50 -0700 Message-Id: <6.0.0.22.2.20040323145550.030d5390@pop.centrexcc.com> X-Sender: centrex@hosting.telus.net@pop.centrexcc.com (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.0.0.22 Date: Tue, 23 Mar 2004 14:56:39 -0700 To: oracle-l@freelists.org From: Wolfgang Breitling Subject: Re: update year in date column In-Reply-To: <20040323200047.32877.qmail@web14422.mail.yahoo.com> References: <20040323200047.32877.qmail@web14422.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1630 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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@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 -----------------------------------------------------------------