RE: update year in date column
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Mar 2004 16:16:14 -0500
Barbara,
To be safe, you may want to do something like this.
Suppose you have a table called CRTEST with data that looks like:
CUSNO CUSNAME CRDATE
1 SMITH 02/21/2099
2 JONES 05/15/2099
3 JOHNSON 08/01/2099
And you know those dates are 1000 years too large.
I'd do this:
UPDATE CRTEST
SET CRDATE = CRDATE - (CRDATE - TO_DATE(TO_CHAR(CRDATE,'MM/DD')||'/'||(TO_CHAR(C
RDATE,'YYYY')-1000)||to_char(crdate,'HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'))
Since Oracle does date calculation on days, and years can be somewhat more
difficult to precisely define, particularly over a 1,000 years, I wrote the
above to force Oracle to do the date arithmetic.
So, what that ugly beast does is it picks apart the date, sets aside the MM/DD
portion and the HH24:MI:SS portion, then it subtracts 1000 from the YYYY, and
glues it back together, then subtracts that from the original. That gives the
difference, and that's what it subtracts from the original.
I think it should work, and it should preserve the timestamps.
Hope that helps,
-Mark
-----Original Message-----
From: Barbara Baker [mailto:barbarabbaker@xxxxxxxxx]
Sent: Tuesday, March 23, 2004 3:01 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
