Minus of time value problem?

Question

Using SQL Server 2000

query

Select id, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > Normal_Outtime THEN Cast(Normal_Outtime AS datetime) ELSE DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) END - CASE WHEN DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) < Normal_Intime THEN Cast(Normal_Intime AS datetime) ELSE DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) END - cast(totalLunchtime AS datetime), 8) ELSE '00:00:00' END AS WorkedTime
from table

From the above query, doing outtime - intime - totallunchtime

column datatypes are varchar

Example

Id Intime Outtime totallunchtime

001 09:00:00 21:00:00 01:00:00
002 07:00:00 07:30:00 01:00:00
003 00:00:00 00:00:00 01:00:00

WorkedTime means (outtime - Intime - totalLunchtime)

Getting Output like this

id workedtime

001 11:00:00
002 23:30:00 
003 23:00:00

Only the problem of totallunchtime while minus 00:00:00 it giving 23:00:00 and also for personid 002 it giving 23:30:00 it should give only 00:30:00

Expected output

id workedtime

001 11:00:00
002 00:23:00
003 00:00:00

so on...,

Need Query Help

Answer 1

Would something like this do the job?

declare @a datetime
declare @b datetime
declare @c datetime

set @a = '09:00:00'
set @b = '17:00:00'
set @c = '01:00:00'

select @b - @a - case when datediff(n, @a, @b) < 60 then '00:00:00' else @c end

If you need more than this, then you need to clarify under what conditions the totallunchtime column should be taken into account.