Mohammad Shahrukh Qureshi - 2 months ago 32

SQL Question

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