Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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.
0
anumoses
Asked:
anumoses
  • 3
  • 3
2 Solutions
 
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
0
 
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
0
 
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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