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

SQL> editWrote file afiedt.buf  1  select start_date  2      , end_date  3      , (24 * extract(day from (end_date - start_date) day(9) to second))  4          + extract(hour from (end_date - start_date) day(9) to second)  5          + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR.MINUTE"  6* from tSQL> /START_DATE          END_DATE            HOUR.MINUTE------------------- ------------------- -----------21-06-2011 14:00:00 21-06-2011 16:55:00        2.5521-06-2011 07:00:00 21-06-2011 16:50:00         9.521-06-2011 07:20:00 21-06-2011 16:30:00         9.1

It should be noted for those coming across this code that the decimal portions are ACTUAL minute differences, and not part of an hour. .5, therefore, represents 50 minutes, not 30 minutes.

Try this

round(to_number(end_time - start_time) * 24)

Oracle represents dates as a number of days, so (end_time-start_time)*24 gives you hours. Let's assume you have this number (eg. 2.9166667) in h column. Then you can easily convert it to the format you want with: FLOOR(h) + (h-FLOOR(h))/100*60.

Example:

WITH diff AS (    SELECT (TO_DATE('21-06-2011 16:55:00', 'DD-MM-YYYY HH24:MI:SS') - TO_DATE('21-06-2011 14:00:00', 'DD-MM-YYYY HH24:MI:SS'))*24 h    FROM dual) SELECT FLOOR(h) + (h-FLOOR(h))/100*60FROM diff

In your case:

SELECT start_time, end_time,    FLOOR((end_time-start_time)*24) + ((end_time-start_time)*24-FLOOR((end_time-start_time)*24))/100*60 AS hours_diffFROM come_leav