• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

SQL UPDATE COMMAND

Hi
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
Raj

.


0
nrajasekhar7
Asked:
nrajasekhar7
  • 2
1 Solution
 
Ivo StoykovCommented:
try this:

HTH
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

0
 
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';
0
 
cyberkiwiCommented:
You can remove it using this fragment

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

I think it explains itself
0
 
cyberkiwiCommented:
Hi there,

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

Update...
set time_interval = time_interval - Interval '59' minutes
where EXTRACT(Minute FROM(time_interval)) = 59
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now