server sql 时间差分钟_计算SQL Server中的时间差(以分钟为单位)

以下按预期工作：

SELECT Diff = CASE DATEDIFF(HOUR, StartTime, EndTime)

WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10))

ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) +

REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) +

+ ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10))

END

FROM (VALUES

(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),

(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),

(CAST('10:45' AS TIME), CAST('11:59' AS TIME))

) t (StartTime, EndTime);

要获得24列,您可以使用24个案例表达式,例如：

SELECT [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0

THEN DATEDIFF(MINUTE, StartTime, EndTime)

ELSE 60 - DATEPART(MINUTE, StartTime)

END,

[1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1

THEN DATEPART(MINUTE, EndTime)

WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60

END,

[2] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 2

THEN DATEPART(MINUTE, EndTime)

WHEN DATEDIFF(HOUR, StartTime, EndTime) > 2 THEN 60

END -- ETC

FROM (VALUES

(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),

(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),

(CAST('10:45' AS TIME), CAST('11:59' AS TIME))

) t (StartTime, EndTime);

以下内容也有效,并且最终可能会比重复使用相同的case表达式更短：

WITH Numbers (Number) AS

( SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1

FROM (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N)

CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N)

), YourData AS

( SELECT StartTime, EndTime

FROM (VALUES

(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),

(CAST('09:45' AS TIME), CAST('18:59' AS TIME)),

(CAST('10:45' AS TIME), CAST('11:59' AS TIME))

) AS t (StartTime, EndTime)

), PivotData AS

( SELECT t.StartTime,

t.EndTime,

n.Number,

MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime)

WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime)

WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number THEN DATEPART(MINUTE, EndTime)

ELSE 60

END

FROM YourData AS t

INNER JOIN Numbers AS n

ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime)

)

SELECT *

FROM PivotData AS d

PIVOT

( MAX(MinuteDiff)

FOR Number IN

( [0], [1], [2], [3], [4], [5],

[6], [7], [8], [9], [10], [11],

[12], [13], [14], [15], [16], [17],

[18], [19], [20], [21], [22], [23]

)

) AS pvt;

它通过连接到24个数字的表来工作,因此不需要重复case表达式,然后使用PIVOT将这24个数字滚动到列中