计算 SQL Server 中的时差（以分钟为单位）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26991807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate time difference in minutes in SQL Server
提问by prabu R
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 time difference between 11:15 and 12:00, 12:00 and 13:00, 13:00 and 13:15 respectively.
我需要第一行的输出为 45,60,15，分别对应于 11:15 和 12:00、12:00 和 13:00、13:00 和 13:15 之间的时差。
采纳答案by GarethD
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 expressions, something like:
要获得 24 列，您可以使用 24 个 case 表达式，例如：
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 expression over and over:
以下也有效，并且可能比一遍又一遍地重复相同的 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;
It works by joining to a table of 24 numbers, so the case expression doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT
它的工作原理是加入一个包含 24 个数字的表，因此不需要重复 case 表达式，然后使用将这 24 个数字滚动回列 PIVOT
回答by Veera
回答by masehhat
You can use DATEDIFF(it is a built-in function) and % (for scale calculation) and CONCAT for make result to only one column
您可以使用 DATEDIFF（它是一个内置函数）和 %（用于比例计算）和 CONCAT 使结果仅生成一列
select CONCAT('Month: ',MonthDiff,' Days: ' , DayDiff,' Minutes: ',MinuteDiff,' Seconds: ',SecondDiff) as T from
(SELECT DATEDIFF(MONTH, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 12 as MonthDiff,
DATEDIFF(DAY, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 30 as DayDiff,
DATEDIFF(HOUR, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 24 as HourDiff,
DATEDIFF(MINUTE, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS MinuteDiff,
DATEDIFF(SECOND, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS SecondDiff) tbl
回答by stanley mbote
Apart from the DATEDIFF you can also use the TIMEDIFF function or the TIMESTAMPDIFF.
除了 DATEDIFF，您还可以使用 TIMEDIFF 函数或 TIMESTAMPDIFF。
EXAMPLE
例子
SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT
TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds';
RESULTS
结果
TIMEDIFF : 36:15:35
Hours : -36
Minutes : -2175
Seconds : -130535
回答by SonalPM
Try this..
尝试这个..
select starttime,endtime, case
when DATEDIFF(minute,starttime,endtime) < 60 then DATEDIFF(minute,starttime,endtime)
when DATEDIFF(minute,starttime,endtime) >= 60
then '60,'+ cast( (cast(DATEDIFF(minute,starttime,endtime) as int )-60) as nvarchar(50) )
end from TestTable123416
All You need is DateDiff..
您只需要DateDiff..
