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

I need the time difference between two times in minutes. I am having the start time and end time as shown below:

start time | End Time

11:15:00 | 13:15:00

10:45:00 | 18:59:00

I need the output for first row as 45,60,15 which corresponds to the tine difference between 11:15 and 12:00, 12:00 and 13:00, 13:00 and 13:15 respectively.

Anybody please help out. Thanks in advance.

解决方案

The following works as expected:

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);

To get 24 columns, you could use 24 case statements, something like:

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);

The following also works, and may end up shorter than repeating the same case statement over and over:

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;

It works by joining to a table of 24 numbers, so the case statement doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT