Home » SQL & PL/SQL » SQL & PL/SQL » Correcting the year in a timestamp field
Correcting the year in a timestamp field [message #213055] Tue, 09 January 2007 04:34 Go to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
I concatenated a date field (charge_date) and a varchar2 (charge_time) field which contains a time, to update a field date_time of type TIMESTAMP with the result, using:

begin

MERGE INTO cdrdata_new a
USING
(
SELECT rowid, to_date(to_char(to_date(charge_date,'dd-mon-rr')||' '|| charge_time),'dd-mon-rrrr hh24miss') ntime
FROM charges
where charge_date = '02-jan-2006') b
ON (a.rowid = b.rowid)
WHEN MATCHED THEN
UPDATE SET a.date_time = ntime;
commit;
end;

as a result the timestamp field turned out e.g. 01/02/0006 12:30:01 AM i.e. with the year incorrect. I now need to go back to correct the year for those records (Jan 05 - Jun 06) with 5 million+ records per day. Is there a faster way to do this than to re-run this script (corrected of course)? I also tried ADD_MONTHS() which worked but takes very long as well. to do this update for one day takes about 30 minutes.

please help, thanks.
Re: Correcting the year in a timestamp field [message #213068 is a reply to message #213055] Tue, 09 January 2007 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is the line thats causing your problems:
to_date(to_char(to_date(charge_date,'dd-mon-rr')||' '|| charge_time),'dd-mon-rrrr hh24miss')

You are performing a TO_DATE on a date field (forcing it to do an implicit type conversion to a VC, and then do a TO_CHAR on that without specifying a date mask. I'm not suprised you loose the format in there.

Try this:
to_date(to_char(charge_date,'dd-mon-rrrr')||' '|| charge_time),'dd-mon-rrrr hh24miss')
, assuming that charge_time is in the format HH24MISS
Re: Correcting the year in a timestamp field [message #213083 is a reply to message #213068] Tue, 09 January 2007 07:23 Go to previous messageGo to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
thanks. I already identified that problem and rectified it. The challenege is now to go back and correct the data. running the script as it is takes 30 minutes+ for just one day. I wanted to know if there's a quicker way to access the year in that timestamp and change it from i.e. from 0005 to 2005.
Re: Correcting the year in a timestamp field [message #213092 is a reply to message #213083] Tue, 09 January 2007 08:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - misread the problem.
I can't think of a quicker way of doing it than this. How long does this take (on a test Db ideally):
UPDATE crdata_new
SET    charge_date = charge_date + 730487
where  charge_date < to_date('01-jan-0100','dd-mon-yyyy');

730487 is the number of days in 2000 years (from
  1* select to_Date('9-jan-2007','dd-mon-yyyy') - to_Date('9-jan-0007','dd-mon-yyyy') from dual
SQL> /

TO_DATE('9-JAN-2007','DD-MON-YYYY')-TO_DATE('9-JAN-0007','DD-MON-YYYY')
-----------------------------------------------------------------------
                                                                 730487
)

Are there any indexes on the charge_Date field? If so, and if you're updating more than about 5% of the table, the query would probably run faster with the index disabled

Do any triggers fire from this column - if so, can you disable them?

Re: Correcting the year in a timestamp field [message #213268 is a reply to message #213092] Wed, 10 January 2007 02:09 Go to previous message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
thanks a lot JRowbottom, adding 730487 days to my date_time field is definitely my solution!
Previous Topic: How to solve Insertion Problem while using sequence.?
Next Topic: pl/sql completed successfully with showing result
Goto Forum:
  


Current Time: Wed Dec 07 18:35:49 CST 2016

Total time taken to generate the page: 0.08854 seconds