将整数的SUM转换为HH:MM格式

Question

我将时间以分钟为单位存储在整数数据类型中，该数据类型必须以HH:MM格式显示。例如：如果总分钟数为80，则应转换为01:20。

select SUM(OTTime) from dbo.TableOT where ....

我尝试了一些查询，但没有得到确切的结果。

更新查询：

SELECT SUM(t.OTTime),d.combovalue
FROM   dbo.employee e 
join dbo.OT t
on e.id = t.employeeid
JOIN dbo.combovalues d 
ON e.department = d.id 
GROUP By d.combovalue

Answer 1

试试这个单一查询：

DECLARE @Duration int
SET @Duration= 4000 
SELECT CAST( CAST((@Duration) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST((@Duration) AS int) % 60 AS varchar(2)),2)

更新查询：

SELECT d.combovalue,CAST(CAST((SUM(t.OTTime)) AS int) / 60 AS varchar) + ':'  
+ right('0' + CAST(CAST((SUM(t.OTTime)) AS int) % 60 AS varchar(2)),2) 
FROM dbo.employee e join dbo.OT t on e.id = t.employeeid 
JOIN dbo.combovalues d ON e.department = d.id 
GROUP By d.combovalue

Answer 2

您可以在下面的查询中使用SUM(OTTime(和from子句，而不是@minutes变量

DECLARE @minutes INT=80
SELECT CASE WHEN @minutes >= 60 THEN  (SELECT CAST((@minutes / 60) AS VARCHAR(2)) + ':' +  CASE WHEN (@minutes % 60) > 0 THEN CAST((@minutes % 60) AS VARCHAR(2))
ELSE   '' END)
ELSE CAST((@minutes % 60) AS VARCHAR(2)) END

例如：

SELECT CASE WHEN SUM(OTTime) >= 60 THEN  (SELECT CAST((SUM(OTTime) / 60) AS VARCHAR(2)) + ':' +  CASE WHEN (SUM(OTTime) % 60) > 0 THEN CAST((SUM(OTTime) % 60) AS VARCHAR(2))
ELSE   '' END)
ELSE CAST((SUM(OTTime) % 60) AS VARCHAR(2)) END
from dbo.TableOT where ....

创建SCALAR函数并通过

select dbo.Minutes_to_HrsMts (SUM(OTTime)) from dbo.TableOT where ....

功能：

CREATE Function dbo.Minutes_to_HrsMts (@minutes INT)
RETURNS nvarchar(30)
AS
BEGIN
declare @hours  nvarchar(20)
SET @hours = 
CASE WHEN @minutes >= 60 THEN
(SELECT CAST((@minutes / 60) AS VARCHAR(2)) + ':' +  
CASE WHEN (@minutes % 60) > 0 THEN
CAST((@minutes % 60) AS VARCHAR(2)) 
ELSE
''
END)
ELSE 
CAST((@minutes % 60) AS VARCHAR(2)) 
END
return @hours
END

Answer 3

首先，我建议使用十进制小时。简单得多：

SELECT d.combovalue, SUM(t.OTTime) / 60.0
FROM dbo.employee e JOIN
dbo.OT t 
ON e.id = t.employeeid JOIN 
dbo.combovalues d
ON e.department = d.id
GROUP By d.combovalue;

但这不是你的问题。如果你知道永远不会超过24小时，你可以使用TIME数据类型：

CONVERT(VARCHAR(5), DATEADD(minute, SUM(t.OTTime), 0), 8)

如果这太危险了，那么你需要转换成一个字符串：

CONCAT(FORMAT(SUM(t.OTTime) / 60, '00'), ':', FORMAT(SUM(t.OTTime) % 60, '00'))