sql - [datetime2](7) 的 DateDiff，采用 hh:mm;ss 格式

Question

CREATE TABLE dbo.Duration     (startDate datetime2, endDate datetime2);

INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-06 16:42:09', '2007-05-07 11:10:08');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 08:30:09', '2007-05-07 12:12:43');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 11:35:09', '2007-05-07 17:13:39');  

以下是要根据预期结果修改的查询

SELECT   DATEDIFF(day, startDate, endDate) AS Duration      FROM dbo.Duration;  

预期结果：

19:32:01
04:18:34
06:22:29

标签： sqlsql-server

Answer 1

您可以尝试以下查询。

SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS,startDate, endDate), 0), 108) AS TimeDiff from Duration