check the time periods available in the SQL table

Question

I am developing a schedule for a software that manages schedules available for scheduling, it seems strange that I post this here more by incredible that it seems not found anything of the kind here on the web, well my scenario is as follows:

I have a table with the following structure:

CREATE TABLE cad_compromisso
(
  id serial NOT NULL,
  data_entrada date,
  nome_func character varying(50),
  servico character varying(100),
  horaini time without time zone,
  horafin time without time zone,
  nome_cli character varying(100)
)

where in the same I keep the date that was scheduled the period that the client will be served, that in the case are the fields "horaini" and "horafin", so far so good, now the problem is that, every time the attendant tries to create a new commitment, it is necessary to verify if there is already any appointment scheduled within the informed period. So far I haven’t been able to leave 100%,

as much as I got was this:

select * from cad_compromisso
   where horafin <= '" & cmbhorafinal.Text & "'
   and horaini >= '" & cmbhorario.Text & "'
   or data_entrada = '" & calendario.Value & "'
   and nome_func = '" & cmbfuncionario.Text & "'
   and horafin > '" & cmbhorario.Text & "'
   and horaini < '" & cmbhorafinal.Text & "'
order by horaini "

If you could send a light I’d appreciate it...

Answer 1

You can just try:

select *
from cad_compromisso
where data_entrada = '" & calendario.Value & "'
  and nome_func = '" & cmbfuncionario.Text & "'
  and (    '" & cmbhorario.Text & "'   between horaini and horafin 
        or '" & cmbhorafinal.Text & "' between horaini and horafin
        or ( horaini > '" & cmbhorario.Text & "' and horafin < '" & cmbhorafinal.Text & "')
  )

The logic behind the instruction is this: I consider that there is already a conflict with another commitment when the date is the same, the official is the same and there is a time overlap.