Calculate the time difference without giving the date

Question

I’m trying to figure out the difference between two hours. However, I would not like to give the dates. I would like to know for example that between 23:00 and 01:00 it was 120 minutes.

What I tried to do and got something like that was so but I had to inform the dates:

DECLARE @antes DATETIME = '2016-10-20 23:00:00.000';

DECLARE @depois DATETIME = '2016-10-21 01:00:00.000';


SELECT  CONVERT(VARCHAR, DATEDIFF(HOUR, @antes, @depois)) + 'H'
        + RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(MINUTE, @antes, @depois) % 60), 2) + ':'
        + RIGHT('00' + CONVERT(VARCHAR, DATEDIFF(SECOND, @antes, @depois) % 60), 2);

Answer 1

The problem is that you are working with different dates and wanting to do the calculations only with the hours. Considering the problem at hand, considering your words:

I would like to know for example that between 23:00 to 01:00 were passed 120 minutes.

I made an example that is available on SQL Fiddle and demonstrated below. Right after the consultation demonstration, I suggest creating and using a customized query.

MS SQL Server 2017 Schema Setup:

CREATE TABLE hora_sem_data (
    codigo int NOT NULL,
    horaInicial varchar(5) NULL,
    horaFinal varchar(5) NULL
);

INSERT INTO hora_sem_data (codigo,horaInicial,horaFinal) VALUES
     (1,'23:00','01:00'),
     (2,'03:00','02:00'),
     (3,'02:00','03:00');

Consultation 1:

Select *,
IIF (cast(horaFinal as time) < cast(horaInicial as time), 
         DATEDIFF(MINUTE, cast(horaInicial as datetime), DateAdd(day, 1, cast(horaFinal as datetime))), 
         DATEDIFF(MINUTE, cast(horaInicial as datetime), cast(horaFinal as datetime))
         )  minutos_passados
from hora_sem_data hsd 

Upshot:

code	opening hours	horaFinal	last
1	23:00	01:00	120
2	03:00	02:00	1380
3	02:00	03:00	60

Explaining the IIF function Used:

The Function IIF:
Syntax: IIF( boolean_expression, true_value, false_value )
The function IIF is checking whether the final time is less than the initial time, if that check is true, then these are different day dates making it necessary to convert the fields through the function cast, turning them into datetime so we can use the functions DateDiff and DateAdd next.
Since the first expression will be returned when the verification expression is true, the function DateAdd to add 1 day to horaFinal thus making it a "final hour" possible to be used to calculate the difference, according to the DATEDIFF(MINUTE, cast(horaInicial as datetime), DateAdd(day, 1, cast(horaFinal as datetime))) representing the true_value of the IFF function. Should the outcome of boolean_expression return false, means that the hours must be considered to be being time of the same day, then we have the stretch DATEDIFF(MINUTE, cast(horaInicial as datetime), cast(horaFinal as datetime)) being executed.

If you would like to read more about the functions of sqlserver used, you can see on the microsoft website IIF, Datediff and Dateadd.

Using a custom function:

I considered the possibility of reporting two times '23:00' and '01:00', the first parameter being the initial time and the second the final time. When the final time is apparently minor that the initial time, as occurs in the example, will mean that it must consider that this final time refers to the later day and must convert the variables to datetime to perform the calculations using the Datediff and Dateadd. functions as demonstrated in this function suggestion:

create function getMinutos(@antes time, @depois time)
Returns int
Begin
    DECLARE @antesDateTime datetime = Cast(@antes as datetime);
    DECLARE @depoisDateTime datetime = Cast(@depois as datetime);
    DECLARE @Result int;

    SELECT @Result =
    IIF (@depoisDateTime < @antesDateTime, 
         DATEDIFF(MINUTE, @antesDateTime, DateAdd(day, 1, @depoisDateTime)), 
         DATEDIFF(MINUTE, @antesDateTime, @depoisDateTime)
         ) 
    Return  @Result   
End;

Using the function in an SQL query:

Select *, dbo.getMinutos(cast(horaInicial as time), cast(horaFinal as time) ) minutos_passados
from hora_sem_data hsd 

Upshot:

code	opening hours	horaFinal	minutes
1	23:00	01:00	120
2	03:00	02:00	1380
3	02:00	03:00	60

Note that the result obtained with the custom function is equal to the result of the SQL query demonstrated in Fiddle.

Why use a custom function?
Enables code reuse and makes the SQL query more readable, for example.

Some examples of using the function in isolation:
When executing:

DECLARE @antes TIME = '23:00';
DECLARE @depois TIME = '01:00';
Select dbo.getMinutos(@antes,@depois) as minutos

Results:

minutes
120

When executing, they will be considered different days:

DECLARE @antes TIME = '03:00';
DECLARE @depois TIME = '02:00';
Select dbo.getMinutos(@antes,@depois) as minutos   

Upshot:

minutes
1380

When executing, it will be considered as hours of the even day:

DECLARE @antes TIME = '02:00';
DECLARE @depois TIME = '03:00';
Select dbo.getMinutos(@antes,@depois) as minutos

Upshot:

minutes
60

You will need to be careful with business logic, because if it is possible to differentiate between times that refer to several days, you will need to make adjustments to the solution. I believe that the ideal would be for you to work primarily with fields of the type datetime.