oracle date question

This is a closed question.

http://www.experts-exchange.com/Database/Oracle/Q_28540770.html#a40392275

drop table tab1 purge;
create table tab1(start_time date, end_time date);

Insert into TAB1
   (start_TIME, end_TIME)
 Values
   (
   TO_DATE('09/01/2010 15:30:00', 'MM/DD/YYYY HH24:MI:SS'),
   TO_DATE('02/01/2008 19:30:00', 'MM/DD/YYYY HH24:MI:SS'));

commit;

select 
  extract(day from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) days,
  extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) hours,
  extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) minutes,
  extract(second from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) seconds
from tab1

Open in new window


I need to add hours and minutes. eg 6 hrs and 30 mins I need to get 630
and if minutes is 0 then it has to be just hours like 6. Help appreciated.
LVL 6
anumosesAsked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
Maybe just concatenate them and add a case?

...
extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) ||
case when  extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) > 0 then extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) end

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
anumosesAuthor Commented:
I had tried doing the below that did not work.

case when
extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')
        -to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) = 0 then
extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')
          -to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day'))
when extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')
        -to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) > 0 then      
extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')
          -to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day'))
extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')
        -to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) end diff      

Thanks though
slightwv (䄆 Netminder) Commented:
It is still basic formatting and concatenation.

Play around with it a little and see what isn't working with it then adjust.

select 
  trim(extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day'))) || 
  case when trim(to_char(extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')),'00'))  > 0 then 
  trim(to_char(extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')),'00'))
  end  mytime
from tab1
/

Open in new window

Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

anumosesAuthor Commented:
In this script the 3rd line is giving wrong data. -30 for minutes.
slightwv (䄆 Netminder) Commented:
I deleted my last post hoping you hadn't seen it yet.  It was flawed.
anumosesAuthor Commented:
thanks for the help
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.