You can use a function to format the time automatically:
IF OBJECT_ID('dbo.formatar_tempo', 'FN') IS NULL
BEGIN
EXEC('CREATE FUNCTION dbo.formatar_tempo() RETURNS INT AS BEGIN RETURN 1 END');
END;
GO
ALTER FUNCTION dbo.formatar_tempo(@segundos INT)
RETURNS VARCHAR(15)
BEGIN
DECLARE @horas INT,
@minutos INT,
@resultado VARCHAR(15);
SET @horas = @segundos / 3600;
SET @segundos = @segundos - @horas * 3600;
SET @minutos = @segundos / 60;
SET @segundos = @segundos - @minutos * 60;
IF @horas BETWEEN 0 AND 99
BEGIN
SET @resultado = RIGHT('00' + CAST(@horas AS VARCHAR), 2) + ':';
END
ELSE IF @horas >= 100
BEGIN
SET @resultado = CAST(@horas AS VARCHAR) + ':';
END;
SET @resultado = @resultado + RIGHT('00' + ISNULL(CAST(@minutos AS VARCHAR), ''), 2) + ':';
SET @resultado = @resultado + RIGHT('00' + ISNULL(CAST(@segundos AS VARCHAR), ''), 2);
RETURN @resultado;
END;
GO
And to perform the calculation of the difference of the first example:
DECLARE @inicio DATETIME = CONVERT(DATETIME, '11/09/2017 11:35', 103),
@fim DATETIME = CONVERT(DATETIME, '11/09/2017 12:35', 103);
PRINT dbo.formatar_tempo(DATEDIFF(SECOND, @inicio, @fim));
01:00:00
And from the second example:
DECLARE @inicio DATETIME = CONVERT(DATETIME, '11/09/2017 11:35', 103),
@fim DATETIME = CONVERT(DATETIME, '12/09/2017 11:55', 103);
PRINT dbo.formatar_tempo(DATEDIFF(SECOND, @inicio, @fim));
24:20:00
Note that I calculated the difference between seconds of dates (% with%) and used the result in the DATEDIFF(SECOND, @inicio, @fim)
function.
You can create a table with the times and start and end times to test the solution too:
CREATE TABLE horarios(
inicio DATETIME,
fim DATETIME
);
INSERT INTO horarios(inicio, fim)
values(CONVERT(DATETIME, '11/09/2017 11:35', 103), CONVERT(DATETIME, '11/09/2017 12:55', 103)),
(CONVERT(DATETIME, '11/09/2017 11:35', 103), CONVERT(DATETIME, '12/09/2017 11:55', 103));
SELECT CONVERT(VARCHAR, h.inicio, 103) AS inicio,
CONVERT(VARCHAR, h.fim, 103) AS fim,
dbo.formatar_tempo(DATEDIFF(SECOND, h.inicio, h.fim)) AS diferenca
FROM horarios h;