I have a scenario in which for example,my start_date ='12-SEP-2018 00:01:00' and End_date ='13-SEP-2018 14:55:00' . The difference between the 2 dates must be found out in Hours and minutes like'12:20'. This must be achieved in oracle database. I tried using the following logic :
SELECT 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours FROM dual; I was able to get the hour difference but unable to get minutes along with it.
Since you want a string value, an alternative based on your query attempt is to add the difference between your two date values (which is a numeric value, the number of days between them, including fractional days) to an arbitrary fixed date; and then convert the result of that to a string:
SELECT to_char(date '0001-01-01'
+ (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')),
'HH24:MI') as diff
FROM dual;
DIFF
-----
02:30
If the difference can exceed 24 hours then you need to decide how to report that; if you want to include days as a separate figure then you can still use this approach, but need to subtract one (if your fixed date is the first) from the difference before formatting as a string:
SELECT to_char(date '0001-01-01'
+ (to_date('2009-07-08 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi'))
- 1,
'DDD:HH24:MI') as diff
FROM dual;
DIFF
---------
001:02:30
If you want the 'hours' value to be higher instead - e.g. '26:30' in this example - then it gets rather more complicated; I see @MTO has added the 'arithmetic' approach already so I won't repeat that. But then might be better off going down the extract() route (which you should consider anyway as it's more flexible and elegant...)