Calculate Difference between 2 dates (has time in them)

Question

Hello, I would like to make the following calculation.

Starting Date = 11 September 2017 at 11:35 am

Final Date = 11 September 2017 at 12:35 pm

It should appear to me 1:10 am.

Another example:

Starting Date = 11 September 2017 at 11:35 am

Final Date = 12 September 2017 at 11:55 am

It should appear to me 24:20.

Answer 1

Your first example would be 1:00.

It should appear to me 1:10 am.

Try it this way.

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

Answer 2

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 calculate 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 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 (DATEDIFF(SECOND, @inicio, @fim)) and used the result in the function formatar_tempo.

---

You can create a table with the times and start and order to test the solution as well:

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;