MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Contracts (
  id int
, start_date date
, end_date   date
) ;
insert into Contracts( id, start_date, end_date )
values
( 1,'2022-05-01', '2022-05-04' ),
( 2,'2022-05-06', '2022-05-10' ),
( 2000,'2000-05-06', '2000-05-10' ),
( 2050,'2050-05-06', '2050-05-10' ),
( 3,'2022-05-01', '2022-05-10' );
select * from Contracts ;


id
start_date
end_date


1
2022-05-01
2022-05-04

2
2022-05-06
2022-05-10

2000
2000-05-06
2000-05-10

2050
2050-05-06
2050-05-10

3
2022-05-01
2022-05-10


select id
from Contracts 
where 
( start_date <= '2022-05-03' and ( end_date >= '2022-05-03' and end_date <= '2022-05-07' ) ) 
or
( start_date >= '2022-05-03' and end_date <= '2022-05-07' ) 
or
( ( start_date >= '2022-05-03' and start_date <= '2022-05-07' ) and end_date >= '2022-05-07' )
or
( start_date <= '2022-05-03' and end_date >= '2022-05-07' )
;


id


1

2

3


-- previous answer (not quite correct)
SELECT id FROM Contracts WHERE start_date BETWEEN '2022-05-03' and '2022-05-07' ORDER BY start_date;


id


2


-- previous answer (not quite correct)
SELECT id FROM `Contracts` WHERE 
(DATE(start_date) >= '2022-05-03' || DATE(start_date) <= '2022-05-03') ||
(DATE(end_date) >= '2022-05-07' || DATE(end_date) <= '2022-05-07')


id


1

2

2000

2050

3

id	start_date	end_date
1	2022-05-01	2022-05-04
2	2022-05-06	2022-05-10
2000	2000-05-06	2000-05-10
2050	2050-05-06	2050-05-10
3	2022-05-01	2022-05-10