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:
2nd row result should be 9.50 instead of 9.5
Post a Comment