title: Minus of time value issue?
body: <p>Using SQL Server 2000</p>
  
  <p>query</p>
  
  <pre><code>Select id, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) &gt; 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) &lt; 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
  </code></pre>
  
  <p>From the above query, doing outtime - intime - totallunchtime</p>
  
  <p>column datatypes are varchar</p>
  
  <p>Example</p>
  
  <pre><code>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
  </code></pre>
  
  <p>WorkedTime means (outtime - Intime - totalLunchtime)</p>
  
  <p>Getting Output like this</p>
  
  <pre><code>id  |workedtime
  
  001 |11:00:00
  002 |23:30:00 
  003 |23:00:00
  </code></pre>
  
  <p>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</p>
  
  <p>Expected output</p>
  
  <pre><code>id  |workedtime
  
  001 |11:00:00
  002 |00:23:00
  003 |00:00:00
  </code></pre>
  
  <p>so on...,</p>
  
  <p>Need Query Help</p>