ORACLE SQL Range of hours

Question

i have a problem, i have a table with 3 columns

- date - varchar2   - varchar2 -   
| date | start_hour | end_hour |  

I need to make a validation to not overlap hour ranges.
For example:

| date  | start_hour | end_hour |  
| date1 | 09:00AM    | 09:30AM  |  
| date1 | 10:30AM    | 11:30AM  |  
| date1 | 01:00PM    | 03:00PM  |  

Assuming the date is the same for the 3 rows.
What i need is, to no overlap these ranges
I cant insert a ranges like this
start_hour = 08:00AM and end_hour = 09:20AM, because, a range between 09:00AM and 09:30AM already exist, so, the new range clashes with the range who exist in the table. I tried so many querys, with not between, the end_hour i insert need to be less than the start_hour from a table. Anyone have an idea how to do it?

Answer 1

i already found the solution to my problem, as a recomendation from a comment in my question, when i changed the format of the hours, and the format of the new hours, it worked perfectly. Here is the code for someone who have this same issue in the future.

DECLARE
  l_count NUMBER(1) := 0;
BEGIN

  SELECT COUNT(*)
  INTO   l_count
  FROM   table
  WHERE  start_hour <= :new_start_hour
  AND    end_hour   >= :new_end_hour
  AND    date = :date
  AND    ROWNUM     = 1;
  dbms_output.put_line(l_count);

END;
/

Thanks for all our help.

Answer 2

I assume that you have converted your time format to hh24:mi

maybe this could help:

with tab as(
select 'date1' as dat,  '09:00' as  start_hour, '09:30' as end_hour from dual union all
select 'date1' as dat,  '10:30' as  start_hour, '11:30' as end_hour from dual union all
select 'date1' as dat,  '13:00' as  start_hour, '15:00' as end_hour from dual 
)
SELECT COUNT(*)
  FROM   tab
  WHERE  start_hour <= '09:10' --:new_end_hour
  AND    end_hour   >= '07:00' --:new_start_hour
  AND    dat = 'date1'
  ;

or you can use between to check it start_hour or end_hour ist between the values

with tab as(
select 'date1' as dat,  '09:00' as  start_hour, '09:30' as end_hour from dual union all
select 'date1' as dat,  '10:30' as  start_hour, '11:30' as end_hour from dual union all
select 'date1' as dat,  '13:00' as  start_hour, '15:00' as end_hour from dual 
)
SELECT COUNT(*)
  FROM   tab
  WHERE  ('09:00' between start_hour and end_hour
  or    '09:10' between start_hour and end_hour
  )
  AND    dat = 'date1'
  ;

db<>fiddle here