Select period between times

Navigation
2

I'm trying to set up a sql to check if there is any record between informed times, but it's not coming out, I've done some tests but I have not been successful.

For example, a bank record that contains these two times, as initial and final respectively:

  

HoraIni: 8:00 and End Time: 8:30

Any period that begins or ends during this reporting period must be selected / returned in sql 

Periodos que devem retornar o registro citado a cima
7:45 até 8:15
8:00 até 8:15

But periods like these should return nothing: 

Periodos sem retorno
7:45 até 8:00
8:30 até 8:45

It's a logic problem, where I can not hit maior, menor e igual to return what I need.

The bank is MYSQL the fields are TIME and this select will run in a Java function, this part is not the problem, but it stays here to solve any doubts.

I hope it has become clear, I have already looked at several examples and found nothing, if you are careful to detail that the times are equal to start and end, where the same time that ends one record starts another.

    
asked by anonymous 06.03.2018 / 00:37

1 answer

2

You need records where Start time is between 8 and 8:30, OR, end time is between 8 and 8:30

So, we can have the following query: 

Select * from tabela 
where 
(horaini >= '8:00' and horaini <= '8:30')
 OR
(horafim >= '8:00' and horafim <= '8:30')
  

ps. You have not put structure of the tables, not even the query you are trying, so I just made an example with the data that was passed. If you need more details, edit the question by adding what is missing.

Edit: 

Select * from tabela 
where 
(horaini >= '8:00' and horaini < '8:30')
 OR
(horafim > '8:00' and horafim <= '8:30')
    
06.03.2018 / 04:12
Error Entity Framework Core Convert Date, time and minute to "time"