Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajarajan.

Asked: September 21, 2017 - 4:10 pm UTC

Last updated: September 21, 2017 - 4:38 pm UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

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

with LiveSQL Test Case:

and Chris said...

Those aren't times! They're numbers!

If you want to do time arithmetic, either

Make them dates

When 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 intervals

select 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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rajarajan Mohanraj, September 21, 2017 - 4:41 pm UTC

Thanks for the responce

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library