DATEDIFF - 以小时和分钟显示结果

I am using the below to determine the minute between two times,

我使用以下来确定两次之间的分钟，

DateDiff(Minute, [MondayOpenTime] , [MondayCloseTime])

For a start time of 09:00 and a Close time of 17:30 it returns the value 510

对于09:00的开始时间和17:30的关闭时间，它返回值510

Or if i use:

或者，如果我使用：

DateDiff(hour, [MondayOpenTime] , [MondayCloseTime])

It returns 8,

它返回8，

How do i get it to return the hours and minutes like 08:30

如何让它像08:30一样返回小时和分钟

Using the minute, and then divide by 60, gives 8.5,

使用分钟，然后除以60，得到8.5，

Note. I have been through many of the similar questions to this, if there is one with an exact answer i will delete this, but i cannot find any answers as of yet

注意。我已经经历了很多类似的问题，如果有一个确切的答案我会删除这个，但我找不到任何答案

2 个解决方案

#1

You can calculate the hours by division as you stated. You can then use modulo to calculate the minutes.

您可以按照您的说明按分部计算小时数。然后，您可以使用modulo来计算分钟数。

declare @MondayOpenTime time = '09:00'
    , @MondayCloseTime time = '17:30'

select convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) / 60) + ':' + right('0' + convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) % 60), 2)

#2

For datetime(2) datatypes, the following could be used:

对于datetime（2）数据类型，可以使用以下内容：

format([MondayCloseTime] - [MondayOpenTime], 'HH:mm')

Provided that the dates fall on the same day. (since these are opening times, that shouldn't be a problem)

前提是日期属于同一天。（因为这些是开放时间，这应该不是问题）

As Sean pointed out: for time datatypes, subtraction isn't directly possible and an extra cast is needed:

正如Sean所指出的：对于时间数据类型，减法不是直接可能的，需要额外的强制转换：

format(cast([MondayCloseTime] as datetime) - cast([MondayOpenTime] as datetime), 'HH:mm')

(sidenote: format is only available on sql-server 2012 and higher)

（旁注：格式仅适用于sql-server 2012及更高版本）

#1