asked on

oracle date question

This is a closed question.

https://www.experts-exchange.com/questions/28540770/oracle-date-question.html?anchorAnswerId=40392275#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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

In this script the 3rd line is giving wrong data. -30 for minutes.

slightwv (䄆 Netminder)

I deleted my last post hoping you hadn't seen it yet. It was flawed.

anumoses

ASKER

thanks for the help