Minus the time problem?

Question

Using SQL Sever 2005

From the below Query Am getting total worked time means Outtime - Intime, Suppose when I minus the Next day time means showing wrong time

Query

Select ID, Normal_Intime, Normal_Outtime, Date, Intime, outtime, CONVERT(char(8), CASE WHEN InTime < Outtime THEN CASE WHEN OutTime > Normal_Outtime THEN CAST(Normal_Outtime AS datetime) ELSE CAST(Outtime AS datetime) END - CASE WHEN InTime < Normal_Intime THEN CAST(Normal_Intime AS datetime) ELSE CAST(Intime AS datetime) END ELSE CASE WHEN InTime < Normal_Intime THEN CAST(Normal_Intime AS datetime) ELSE CAST(Intime AS datetime) END - CASE WHEN OutTime > Normal_Outtime THEN CAST(Normal_Outtime AS datetime) ELSE CAST(Outtime AS datetime) END END, 8) AS workedtime from table1

Output

ID Normal_Intime, Normal_Outtime, Date Intime, Outtime, Worktime

01  10:00:00 19:00:00 01/09/2009 08:20:56 15:40:15 05:40:15
01  18:00:00 05:00:00 02/09/2009 15:00:59 08:20:16 13:00:00

First One is Correct, Second Worktime should give 11:00:00 Hours only because Intime 18:00:00 and Outtime is 05:00:00 (next day 05:00:00 am), Now It is working as 18:00:00 - 05:00:00 giving as 13:00:00. It should give only 11:00:00

How to minus the next day time.

Need Query Help

Answer 1

Don't use substraction, use the datediff function instead.

Answer 2

in case out time is less than in time then add 24 to it before minus