将时间计算转换为HH:MM格式的时间

Question

我希望在SQL Server中创建一个计算，该计算将显示HH_MM中time列的时差。

例如，

之间的小时和分钟之差

5:45 PM - 3:30 PM = 2:15 (desired output)

我能得到的最接近的是:

CONVERT(TIME,cals_END_time - cals_START_time) = 02:15:00.0000000

我如何把它"修剪"到2:15?

Answer 1

假设列为datetime，使用format函数格式化时间值:

select format(cast(cals_end_time - cals_start_time as time), N'h:mm')
from (values
(cast('2022-07-07 15:30:00' as datetime), cast('2022-07-07 17:45:00' as datetime))
) as tests(cals_start_time, cals_end_time)

,DB&lt的在小提琴

Answer 2

如果要添加/减去日期/日期时间，则需要使用相关函数，如下所示:

select *, convert(varchar(5), dateadd(minute, datediff(minute, [TimeOut], TimeIn), convert(time, '00:00'))) TimeDifference
-- , CONVERT(TIME, [TimeOut], TimeIn) -- gives an error anyway?
from (
values
(convert(time, '05:45 PM'), convert(time, '03:30 PM'))
) x (TimeIn, [TimeOut]);

的回报:

tbody> <<tr>

TimeIn	TimeOut	TimeDifference
17:45:00.0000000	15:30:00.0000000	02:15