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