CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Anup
Anup

Reputation: 9756

Convert SUM of integer to HH:MM format

I have time stored in minutes in integer datatype which has to be displayed in HH:MM format. For example: if total minutes is 80 then it should convert to 01:20.

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

I have tried some queries but didn't get the exact output.

Updated Query:

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

Upvotes: 0

Views: 435

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

First, I recommend using decimal hours. Much simpler:

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;

But that is not your question. If you knew that there are never more than 24 hours, you could use the TIME data type:

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

If that is too dangerous, then you need to convert to a string:

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

Upvotes: 0

Thiyagu
Thiyagu

Reputation: 1340

Try this Single Query:

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)

For Updated Query:

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

Upvotes: 1

Thiyagu
Thiyagu

Reputation: 1340

Instead of @minutes variable you can use SUM(OTTime) and a from clause in this below Query

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

For Eg:

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

Create a SCALAR Function and pass the

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

Function:

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

Upvotes: 0

Related Questions