SQL Server 中以小时为单位的两次时间之间的时差

Question

我需要小时中两次之间的时差。我有如下所示的start time和end time：

Start time | End Time 
-----------+----------  
23:00:00   | 19:00:00    
23:00:00   | 07:00:00

我需要第一行的输出为 20，第二行的输出为 8。

Answer 1

试试这个：

图式：

create table a(Starttime time,Endtime time)
INSERT INTO a VALUES ('23:00:00','19:00:00')
INSERT INTO a VALUES ('09:00:00','19:00:00')
INSERT INTO a VALUES ('23:00:00','07:00:00')

查询：

select Starttime,Endtime,
CASE WHEN datediff(HOUR,Starttime,Endtime)<0 THEN 24+datediff(HOUR,Starttime,Endtime) 
ELSE datediff(HOUR,Starttime,Endtime) END Diff
FROM A

输出：

|        Starttime |          Endtime | Diff |
|------------------|------------------|------|
| 23:00:00.0000000 | 19:00:00.0000000 |   20 |
| 09:00:00.0000000 | 19:00:00.0000000 |   10 |
| 23:00:00.0000000 | 07:00:00.0000000 |    8 |

Answer 2

使用DATEDIFF：

SELECT
start_time,
end_time,
24 + DATEDIFF(HOUR, start_time, end_time) AS diff_in_hours
FROM yourTable;

演示

Answer 3

根据您的要求进行查询，只需将表名放在"您的表"的位置即可

SELECT Starttime
,Endtime
,CASE 
WHEN DATEDIFF(HOUR, Starttime, Endtime) < 0
THEN 24 + DATEDIFF(HOUR, Starttime, Endtime)
ELSE DATEDIFF(HOUR, Starttime, Endtime)
END Time_Difference
FROM YourTable

Answer 4

使用select case

select case when start_time > end_time
then datediff(hour, start_time , dateadd(hh, 24, end_Time)) 
else datediff(hh, start_time , end_Time) end