MySQL 8.0

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

select version();


version()


8.0.30


create table mystery_table_name
(id serial, name text, startdate date, enddate date);
insert into mystery_table_name values
(1,'Foo','2024-02-01','2024-02-29'),
(2,'Bar','2023-12-25','2024-01-01'),
(3,'Baz','2023-12-25','2023-12-30'),
(4,'Quux','2024-01-31','2024-01-01');

Records: 4  Duplicates: 0  Warnings: 0


with recursive date_range as (
   select id, name, startdate, enddate,
     case when startdate <= enddate then startdate end date_in_range
   from mystery_table_name
   
   union all
   
   select id,name,startdate,enddate,date_in_range + interval 1 day
   from date_range
   where date_in_range < enddate
)
select id, name, startdate, enddate,
  group_concat(case when dayofweek(date_in_range)=1 then date_in_range end order by date_in_range) sundays
from date_range
group by id, name, startdate, enddate
order by id, name, startdate, enddate


id
name
startdate
enddate
sundays


1
Foo
2024-02-01
2024-02-29
2024-02-04,2024-02-11,2024-02-18,2024-02-25

2
Bar
2023-12-25
2024-01-01
2023-12-31

3
Baz
2023-12-25
2023-12-30
null

4
Quux
2024-01-31
2024-01-01
null

id	name	startdate	enddate	sundays
1	Foo	2024-02-01	2024-02-29	2024-02-04,2024-02-11,2024-02-18,2024-02-25
2	Bar	2023-12-25	2024-01-01	2023-12-31
3	Baz	2023-12-25	2023-12-30	null
4	Quux	2024-01-31	2024-01-01	null