Red Devil Red Devil - 4 months ago 11
SQL Question

difference in time is giving incorrect results

I have a table as test

shiftend | out |
---------------------------------------------
15:00:00.0000000 | 2016-07-22 14:42:00 |
16:00:00.0000000 | 2016-07-22 16:06:00 |


Shiftend is having a datatype as time
out is having a datatype as smalldatetime

I am expecting the output as

shiftend | out | Output
-----------------------------------------------------------------------------
15:00:00.0000000 | 2016-07-22 14:42:00 | -00:18:00
16:00:00.0000000 | 2016-07-22 16:06:00 | 00:06:00


I am trying this query:

select shiftend,out,CAST((out-Shiftend) as time(0)) as Output from test
where
CAST(CONVERT(NVARCHAR(10), out, 101) AS SMALLDATETIME) = CAST(CONVERT(NVARCHAR(10),'2016-07-22', 101) AS SMALLDATETIME)


But i am getting the output as

shiftend | out | Output
-----------------------------------------------------------------------------
15:00:00.0000000 | 2016-07-22 14:42:00 | 23:42:00
16:00:00.0000000 | 2016-07-22 16:06:00 | 00:06:00


23:42:00 is incorrect. How to calcualte the time.

Answer

Try the following query:

select 
 case when (cast(out as time) < shiftend) then '-' else '' end + 
 convert(varchar(8), 
  dateadd(minute, 
   abs(
    DATEDIFF(minute, 
     cast(out as time)
    , shiftend)
   )
 ,0)
,108) as Output

Explanation:

  • You're getting the difference between the two dates with DATEDIFF(minute, cast(out as time), shiftend).

  • You need just the time component to avoid going to the previous day, so you use cast(out as time). shiftend as you mentioned is already of datatype time

  • abs returns the absolute value, so -18 becomes 18.

  • Then generate a date by adding the above value as minutes to 00:00:00 using dateadd(minute, [above value], 0)

  • The final convert(varchar(8),____,108) is since you required the output as a time.

  • iif(cast(out as time) < shiftend,'-','') adds negative sign or not to the beginning of the word.

Unfortunately, you can't have negative values in the time datatype. It's turning -18 into 00:00 - 18 which is 23:42. You could:

  • Use datediff and save the difference as the number of seconds for example.
  • Save only the modular difference and have a separate column for deciding whether it's a positive or negative difference.
  • Write your own SQL function for computing this as a varchar as above