# Calculate Difference between 2 dates (have time in them)

Posted on

#### Question :

Hello, I would like to do the following calculated.

Initial date = September 11, 2017 at 11:35

Final Date = September 11, 2017 at 12:35 p.m.

It should appear to me 01:10.

Another example:

Initial date = September 11, 2017 at 11:35

Final Date = September 12, 2017 at 11:55

It should appear to me 24:20.

Your first example would be 1:00.

It should appear to me 01:10.

``````declare @DataInicial datetime = cast('09/11/2017 11:35' as datetime)
declare @Datafinal datetime = cast('09/11/2017 12:35' as datetime)

select CASE WHEN minpart=0
THEN CAST(hourpart as nvarchar(200))+':00'
ELSE CAST((hourpart-1) as nvarchar(200)) + ':'+ CAST(minpart as nvarchar(200))END as 'total time'
from
(
select  DATEDIFF(Hour,@DataInicial, @Datafinal) as hourpart,
DATEDIFF(minute,@DataInicial, @Datafinal) % 60 as minpart
)D
``````

Second example.

``````declare @DataInicial datetime = cast('09/11/2017 11:35' as datetime)
declare @Datafinal datetime = cast('09/12/2017 12:55' as datetime)

select @DataInicial, @Datafinal
select CASE WHEN minpart=0
THEN CAST(hourpart as nvarchar(200))+':00'
ELSE CAST((hourpart-1) as nvarchar(200)) + ':'+ CAST(minpart as nvarchar(200))END as 'total time'
from
(
select  DATEDIFF(Hour,@DataInicial, @Datafinal) as hourpart,
DATEDIFF(minute,@DataInicial, @Datafinal) % 60 as minpart
)D
``````

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,

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);

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;
``````