27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (tStartTime DATETIME,tEndTime DATETIME)
INSERT INTO @t VALUES ('2018-02-28 14:40:35.607','2018-03-15 14:41:36.607')
;WITH cte AS (
SELECT DATEDIFF(hour,tStartTime,tEndTime) AS h
,DATEDIFF(n,tStartTime,tEndTime)-DATEDIFF(hour,tStartTime,tEndTime)*60 AS m
, DATEDIFF(second,tStartTime,tEndTime)-DATEDIFF(n,tStartTime,tEndTime)*60 AS s
FROM @t
)
SELECT LTRIM(h)+':'
+CASE WHEN m>9 THEN LTRIM(m) ELSE '0'+LTRIM(m) END+':'
+CASE WHEN s>9 THEN LTRIM(s) ELSE '0'+LTRIM(s) END AS r
FROM cte
/*
360:01:01
*/
DECLARE @t TABLE (tStartTime DATETIME2,tEndTime DATETIME2)
INSERT INTO @t VALUES ('2018-02-28 14:40:35.607','2018-03-15 14:41:36.607')
SELECT DATEDIFF(hour,tStartTime,tEndTime) AS h
,DATEDIFF(n,tStartTime,tEndTime)-DATEDIFF(hour,tStartTime,tEndTime)*60 AS m
, DATEDIFF(second,tStartTime,tEndTime)-DATEDIFF(n,tStartTime,tEndTime)*60 AS s
FROM @t
/*
h m s
360 1 1
*/