Adsense Ad

Friday 5 October 2018

Oracle SQL: How to subtract 2 dates in oracle to get the result in hour and minute

Objective: To subtract 2 dates and represent the result in hour and minute in one decimal figure.

START_TIME          END_TIME            (END_TIME-START_TIME)*24    
------------------- ------------------- ------------------------      
21-06-2011 14:00:00 21-06-2011 16:55:00  2.9166667      
21-06-2011 07:00:00 21-06-2011 16:50:00  9.8333333      
21-06-2011 07:20:00 21-06-2011 16:30:00  9.1666667  
Required result (end_time-start_time) as below.
16:55- 14:00 = 2.55      
16:50-07:00 = 9.5      
16:30-7:20 = 9.1 and so on.   
Solution:
SQL> select start_date
     , end_date
     , (24 * extract(day from (end_date - start_date) day(9) to second))
      + extract(hour from (end_date - start_date) day(9) to second)
      + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR_MINUTE"
 from table;
/

START_DATE          END_DATE            HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00        2.55
21-06-2011 07:00:00 21-06-2011 16:50:00         9.5
21-06-2011 07:20:00 21-06-2011 16:30:00         9.1

2 comments:

Unknown said...
This comment has been removed by the author.
Unknown said...

2nd row result should be 9.50 instead of 9.5