I have column called time_interval  'dd/mm/yyyy hh24:mi:ss'
having values like  '03/03/2007 00:00:00,03/03/2007 01:00:00, 03/03/2007 02:00:00,03/03/2007 03:59:00, 03/03/2007 04:59:00,03/03/2007 05:59:00,03/03/2007 06:00:00,
03/03/2007 07:59:00  ---- 23:00:00
so here i want t0  update the mintues value  to '00' insted of 59'
so , pls help how to write the update statment  to replace  mintute  value '59' to '00'
where ever in the column have mintute value '59' should be updated by '00' value
like thess '03/03/2007 03:00:00,03/03/2007 04:00:00,03/03/2007 05:00:00,03/03/2007 06:00:00,
03/03/2007 07:00:00  
please help asasp.its urgent..

we are using oracle database

Thanks in advance


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ivo StoykovCommented:
try this:

Ivo Stoykov

update your_table_name
   set time_interval =  time_interval + 1 /24/60
 where substr(to_char(time_interval, 'dd/mm/yyyy hh24:mi:ss'), 15, 2) = '59'

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helena Markováprogrammer-analystCommented:
You can try this:

update your_table
set your_column=to_date(
substr(to_char(your_column,'dd/mm/yyyy hh24:mi:ss'),1,14)||'00:'||to_char(your_column,'ss'),
'dd/mm/yyyy hh24:mi:ss')
where to_char(your_column,'mi')='59';
You can remove it using this fragment

time_interval - Interval EXTRACT(Minute FROM(time_interval)) minutes

I think it explains itself
Hi there,

I see, you don't want to zero all minutes, use this then

set time_interval = time_interval - Interval '59' minutes
where EXTRACT(Minute FROM(time_interval)) = 59
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.