如何在 TSQL 中将分钟数转换为 hh:mm 格式?

2021-12-13 00:00:00 type-conversion tsql sql-server-2008 sql-server datediff

我有一个选择查询，它有 DURATION 列来计算 Minutes 的数量.我想将这些分钟转换为 hh:mm 格式.

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format.

Duration 的值类似于 60, 120,150

Duration has values like 60, 120,150

例如:

60 变为 01:00 小时

120 变为 02:00 小时

150 变为 02:30 小时

此外，这就是我检索DURATION(分钟)的方式

Also, this is how I retrieve DURATION (Minutes)

DATEDIFF(minute, FirstDate,LastDate) as 'Duration (Minutes)'

推荐答案

您可以将持续时间转换为日期，然后对其进行格式化:

You can convert the duration to a date and then format it:

DECLARE
    @FirstDate datetime,
    @LastDate datetime

SELECT
    @FirstDate = '2000-01-01 09:00:00',
    @LastDate = '2000-01-01 11:30:00'

SELECT CONVERT(varchar(12), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30:00:000 */

为了降低精度，修改varchar的大小:

For less precision, modify the size of the varchar: 

SELECT CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30 */

相关文章