sql query date formatting (Hours:Minutes)

Question

How to display values as Hours:Minutes format (eg 77, Result: 1:17)

info :

select 
    SUM([Travel Time] + [Total Productive Time])  
from 
    vAction_Reg 
where 
    incident_id = '10064068'

Result: 77.00

Need to get result in below format

(1:17)

---

Changed Query:

SELECT( SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
    FROM dbo.act_reg AS action 
    INNER JOIN dbo.act_type ON action.act_type_id = dbo.act_type.act_type_id
    WHERE (dbo.act_type.act_type_n IN ('Travel Time')) AND (action.incident_id = inc.incident_id)) AS [Travel Time],
   ( SELECT  ISNULL(SUM(action.service_time), 0) AS Expr1
     FROM  dbo.act_reg AS action 
     INNER JOIN dbo.act_type AS act_type_6 ON action.act_type_id = act_type_6.act_type_id
     WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure')) AND (action.incident_id = inc.incident_id)) AS [Total Productive Time],
     ( SELECT cast(total / 60 as varchar(8)) + ':' + cast(total % 60 as varchar(2)) 
       FROM 
       ( SELECT cast(sum([Travel Time] + [Total Productive Time]) as int) as total) T) AS [Total Service Time]                        
         FROM dbo.incident AS inc 
         INNER JOIN dbo.assyst_usr ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id 
         INNER JOIN dbo.serv_dept ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id  
         WHERE (inc.incident_ref ='64483')

Answer 1

You can divide for hours, mod for remaining minutes:

select 
cast(fld / 60 as varchar(8)) + ':' + cast(fld % 60 as varchar(2))

Update for ?money? - assumes no fractional parts;

select 
    cast(total / 60 as varchar(8)) + ':' + cast(total % 60 as varchar(2)) 
from (
    select 
        cast(sum([Travel Time] + [Total Productive Time]) as int) as total
    from 
        vAction_Reg 
    where 
        incident_id = '10064068'
) T

Answer 2

Create a function as below:

CREATE FUNCTION [dbo].[NumberToTime]
(  
    @Num int
)
RETURNS nvarchar(20)
AS  
BEGIN
DECLARE @TimeDuration AS nvarchar(20)
SET @TimeDuration = (SELECT CAST((@Num / 60) AS VARCHAR(8)) + ':' + 
                CAST((@Num % 60) AS VARCHAR(2)))
RETURN @TimeDuration
END

To use the function:

select 
dbo.NumberToTime(SUM([Travel Time] + [Total Productive Time])) from 
vAction_Reg where 
incident_id = '10064068'

It will return: 1:17