Mohammad Shahrukh Qureshi Mohammad Shahrukh Qureshi - 13 days ago 6
SQL Question

Calculating decimal difference in minutes format

I have Time_In number(7,2) := 12.40

and Time_Out number(7,2) := 23.05

and Total_Hours number(7,2)=Time_Out-Time_In

and Result will be Total_Hours=10.65 which should be 10.25

Can someone give me SQL query to calculate difference between Time_in and Time_out.

Answer

Something like this should work. It's just a hack; you would do MUCH better to change the base table to store times in the DATE datatype.

The idea is, when the minutes on the time_out are less than on time_in, to subtract 1 from hours on the time_out and to add 60 minutes. This is just like subtracting 0.4 when working with decimals. So this is exactly what the solution does.

So, yes, this can be done, and it is easy to do - it is just the wrong thing to do. Good luck!

with 
     inputs ( time_in, time_out ) as (
       select  9.45, 12.06 from dual union all
       select 10.20, 14.02 from dual
     )
select time_in, time_out, 
       case when time_out - trunc(time_out) >= time_in - trunc(time_in)
                   then time_out - time_in
            else time_out - time_in - 0.4 end   as difference
from inputs;


TIME_IN  TIME_OUT  DIFFERENCE
   9.45     12.06        2.21
   10.2     14.32        4.12