Hi Tom,
select '21.00' - '21.30' from dual
I need the time difference as .5 instead of 0.30 for my calculation.
Regards,
Rajarajan.M
Those aren't times! They're numbers!
If you want to do time arithmetic, either
Make them datesWhen you subtract dates, the result is the number of days difference. So assuming you want the result as the number of hours difference including fractions, multiply up by 24, rounding as needed:
select round((to_date('21:00', 'hh24:mi') - to_date('21:30', 'hh24:mi')) * 24, 2)
from dual;
ROUND((TO_DATE('21:00','HH24:MI')-TO_DATE('21:30','HH24:MI'))*24,2)
-0.5
Make them day to second intervalsselect to_dsinterval ('0 21:00:00') - to_dsinterval ('0 21:30:00')
from dual;
TO_DSINTERVAL('021:00:00')-TO_DSINTERVAL('021:30:00')
-00 00:30:00.000000
This time to get the fractional hours, extract the minutes and divide by 60:
select extract( minute from to_dsinterval ('0 21:00:00') - to_dsinterval ('0 21:30:00') ) / 60
from dual;
EXTRACT(MINUTEFROMTO_DSINTERVAL('021:00:00')-TO_DSINTERVAL('021:30:00'))/60
-0.5
If your times can be more than an hour apart you'll need to extract the hours out and add these on too.