如何在SQL中计算2次之间的差异

I have 2 columns in a table i.e. DutyHours(time(7)) and TimeSpentInOffice(time(7)).

我在表中有2列，即DutyHours（时间（7））和TimeSpentInOffice（时间（7））。

How can I calculate the difference between these two times?

如何计算这两次之间的差异？

The datediff function returns in hour, minute, second or day etc but not in time.

datediff函数以小时，分钟，秒或日等方式返回，但不及时返回。

1 个解决方案

#1

DECLARE @null time;
SET @null = '00:00:00';

SELECT DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)

Reference: Time Difference

参考：时差

Edit: As per the comment, if the difference between the end time and the start time might be negative, then you need to use a case statement as such

编辑：根据评论，如果结束时间和开始时间之间的差异可能是负数，那么您需要使用案例陈述

SELECT CASE
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) <=0
           THEN DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) >0
           THEN DATEADD(SECOND,  DATEDIFF(SECOND, End_Time, Start_Time), @null)
       END AS TimeDifference

#1

DECLARE @null time;
SET @null = '00:00:00';

SELECT DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)