SQL:如何从两个不同的列中减去时间数据类型，然后生成sum作为HH:MM:SS。

I have a table (TimeLog) that has 2 columns (StartTime, TIME(0), NOT NULL) and (FinishTime, TIME(0), NOT NULL) I want write a query that will subtract (FinishTime - StartTime) AS ElapsedTime and produce the sum in HH:MM:SS

我有一个表(TimeLog)，它有2列(StartTime, TIME(0)，非NULL)和(FinishTime, TIME(0)， NOT NULL)

I have tried

我有试过

SELECT StartTime
      ,FinishTime
      ,REPLACE(CAST(CAST(DATEDIFF(Hour, StartTime, FinishTime )AS VARCHAR (2)) + ':' + CAST(DATEDIFF(MINUTE, StartTime, FinishTime )AS VARCHAR(2)) + ':'
+ Cast(DATEDIFF(Second, StartTime, FinishTime )AS VARCHAR (2)) AS VARCHAR(8)),'*','00') As ElapsedTime
FROM TimeLog

StartTime   FinishTime  ElapsedTime
08:00:00    08:25:00    0:25:00
08:25:00    09:15:00    1:50:00
09:55:00    12:32:00    3:00:00
12:32:00    14:31:00    2:00:00
12:32:00    13:55:00    1:83:00
09:55:00    11:42:00    2:00:00
07:30:00    08:45:00    1:75:00
07:00:00    07:15:00    0:15:00
07:15:00    08:10:00    1:55:00

But this will only work if ElapsedTime is below an hour. If more than 1 hour has elapsed the result is in incorrect format as I want. Can anyone assist?

但这只在运行时间小于一小时时有效。如果超过1小时，结果按照我想要的格式是不正确的。谁能帮助?

1 个解决方案

#1

You can add the seconds from datediff() to a date of 0 using dateadd(), and convert() that to time(0) data type like so:

您可以使用dateadd()将datediff()的秒添加到日期为0的日期，并将其转换为time(0)数据类型，如下所示:

select 
    StartTime
  , FinishTime
  , ElapsedTime = convert(time(0),dateadd(second,datediff(second,StartTime,FinishTime),0))
  /* for greater than 24 hours: */
  , ElapsedTime = right('0' + convert(varchar(9),(datediff(second,StartTime,FinishTime) / 3600 )),2) + ':' 
                + right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) / 60) % 60 ),2) + ':' 
                + right('0' + convert(varchar(2),(datediff(second,StartTime,FinishTime) % 60 )),2)
from TimeLog

rextester demo: http://rextester.com/KVC7988

rextester演示:http://rextester.com/KVC7988

returns:

+-----------+------------+-------------+
| StartTime | FinishTime | ElapsedTime |
+-----------+------------+-------------+
| 08:00:00  | 08:25:00   | 00:25:00    |
| 08:25:00  | 09:15:00   | 00:50:00    |
| 09:55:00  | 12:32:00   | 02:37:00    |
| 12:32:00  | 14:31:00   | 01:59:00    |
| 12:32:00  | 13:55:00   | 01:23:00    |
| 09:55:00  | 11:42:00   | 01:47:00    |
| 07:30:00  | 08:45:00   | 01:15:00    |
| 07:00:00  | 07:15:00   | 00:15:00    |
| 07:15:00  | 08:10:00   | 00:55:00    |
+-----------+------------+-------------+

I believe that you may have been confused about how datediff() works. datediff(second,StartTime,FinishTime) returns number of seconds between the two times, it is not comparing the numbers in the 'seconds' place of each time.

我相信您可能对datediff()的工作方式感到困惑。datediff(second,StartTime,FinishTime)返回两次之间的秒数，它不是在每次的“秒”位置上比较数字。

#1