将时间计算转换为HH中的时间：mm

发布于 2025-02-13 22:12:04 字数 306 浏览 0 评论 0原文

我希望在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？

原文

I am looking to create a calculation in SQL Server that will display the time difference in HH_MM from a time column.

For example, the difference in hours and minutes between

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

The closest I have been able to get is this:

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

How do I go about 'trimming' it down to 2:15?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

禾厶谷欠 2025-02-20 22:12:06

如果要添加/减法日期/数据时间，则需要使用相关函数进行以下操作：

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

TimeIn	Timeout	TimeDifference
17：45：00.0000000	15：30：00.0000000	02:15

If you want to add/subtract dates/datetimes you need to use the relevant functions to do so as follows:

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

Returns:

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

回复收藏 0 原文

小嗲 2025-02-20 22:12:05

假设列是datetime，请使用格式函数到格式时间值：

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;＆gt; fiddle

Assuming the columns are datetime, use the format function to format the time value:

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<>Fiddle

回复收藏 0 原文

~没有更多了~