From oracle-l-bounce@freelists.org Tue Mar 23 16:52:54 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2NMqsK05037 for ; Tue, 23 Mar 2004 16:52:54 -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 i2NMqro05030 for ; Tue, 23 Mar 2004 16:52:53 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3565D391BB3; Tue, 23 Mar 2004 17:18:48 -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 13791-29; Tue, 23 Mar 2004 17:18:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CF51339509B; Tue, 23 Mar 2004 16:49:25 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 23 Mar 2004 16:48:10 -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 9CEA8391130 for ; Tue, 23 Mar 2004 16:38:44 -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 04479-43 for ; Tue, 23 Mar 2004 16:38:44 -0500 (EST) Received: from marcie.jcpenney.com (146-235-99-56-jcp.jcpenney.com [146.235.99.56]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 808F5394DFD for ; Tue, 23 Mar 2004 16:32:22 -0500 (EST) Received: from i006.jcpenney.com (i006.jcpenney.com [10.33.181.175]) by marcie.jcpenney.com (8.12.10/8.12.10) with ESMTP id i2NLasuP022159 for ; Tue, 23 Mar 2004 15:39:20 -0600 (CST) Received: from rshamsudxp ([10.32.47.33]) by i006.jcpenney.com (iPlanet Messaging Server 5.2 HotFix 1.17 (built Jun 23 2003)) with ESMTPA id <0HV100MSGTAZBB@i006.jcpenney.com> for oracle-l@freelists.org; Tue, 23 Mar 2004 15:35:23 -0600 (CST) Date: Tue, 23 Mar 2004 15:35:24 -0600 From: Riyaj Shamsudeen Subject: RE: update year in date column In-reply-to: <20040323200047.32877.qmail@web14422.mail.yahoo.com> To: oracle-l@freelists.org Message-id: <008401c4111e$c024ba20$212f200a@rshamsudxp> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Mailer: Microsoft Outlook, Build 10.0.3416 Content-Type: multipart/mixed; boundary="----------=_1080077963-12088-37" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Disclaimer: Outbound Correspondence X-VirusScan: JCP virus scan detected no virus X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1614 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rshamsud@jcpenney.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ------------=_1080077963-12088-37 Content-Type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Will this do ? Alter session set nls_date_format='DD-MON-CYYYY-HH24:MI:SS'; select * from mytabdate; CUSTNO CRDATE ---------- --------------------- 1 16-SEP- 2099 00:00:00 1 08-OCT- 2099 00:00:00 update mytabdate set crdate = to_date(to_char(crdate, 'DD-MON-')|| to_char(to_number(to_char(crdate,'yyyy'))-100) || to_char(crdate, ' HH24:MI:SS') ,'DD-MON-YYYY HH24:MI:SS') / 2 rows updated. select * from mytabdate; CUSTNO CRDATE ---------- --------------------- 1 16-SEP- 1999 00:00:00 1 08-OCT- 1999 00:00:00 Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Barbara Baker Sent: Tuesday, March 23, 2004 2:01 PM To: oracle-l@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@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 ----------------------------------------------------------------- ------------=_1080077963-12088-37 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ------------=_1080077963-12088-37-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------