From oracle-l-bounce@freelists.org Tue Mar 23 17:20:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2NNK4d10929 for ; Tue, 23 Mar 2004 17:20:04 -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 i2NNK0o10917 for ; Tue, 23 Mar 2004 17:20:00 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4DAB8390A0F; Tue, 23 Mar 2004 17:31:08 -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 15911-57; Tue, 23 Mar 2004 17:31:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 40A8E394B2D; Tue, 23 Mar 2004 17:17:41 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 23 Mar 2004 17:16:22 -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 8D86C391DB3 for ; Tue, 23 Mar 2004 16:44:25 -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 07091-05 for ; Tue, 23 Mar 2004 16:44:25 -0500 (EST) Received: from dotmail02.dot.state.nc.us (dotmail02.dot.state.nc.us [207.4.62.223]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2C200391B7C for ; Tue, 23 Mar 2004 16:17:52 -0500 (EST) Received: from dot.state.nc.us ([149.168.139.34]) by dotmail02.dot.state.nc.us (Netscape Messaging Server 3.62) with ESMTP id 275 for ; Tue, 23 Mar 2004 16:24:29 -0500 Message-ID: <4060AB3B.C6394D35@dot.state.nc.us> Date: Tue, 23 Mar 2004 16:25:15 -0500 From: Don Jerman Organization: North Carolina Department of Transportation X-Sender: "Don Jerman" X-Mailer: Mozilla 4.7 [en]C-CCK-MCD (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Re: update year in date column References: <20040323200047.32877.qmail@web14422.mail.yahoo.com> Content-Type: multipart/mixed; boundary="------------DE4465020CA7EB745C87CE60" X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1623 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: djerman@dot.state.nc.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org --------------DE4465020CA7EB745C87CE60 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit You could just subtract 36525 and see if that works. Do it in a query first to make sure it's the right number. If you keep to whole numbers the time won't change. SQL> select to_char(sysdate-36525,'MM-DD-YYYY hh24:mi:ss') from dual; TO_CHAR(SYSDATE-365 ------------------- 03-23-1904 16:18:59 Barbara Baker 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 > ----------------------------------------------------------------- -- E-mail correspondence to and from this address may be subject to the North Carolina Public Records Law "NCGS.Ch.132" and may be disclosed to third parties by an authorized state official. -- --------------DE4465020CA7EB745C87CE60 Content-Type: text/x-vcard; charset=us-ascii; name="djerman.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Don Jerman Content-Disposition: attachment; filename="djerman.vcf" begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Database Management Service,SE\Information Technology version:2.1 email;internet:djerman@dot.state.nc.us title:Database Administrator adr;quoted-printable:;;Database Management Service,SE\Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard --------------DE4465020CA7EB745C87CE60-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------