MySQL 8.0

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

CREATE TABLE tbl ( 
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(20),
  startdate DATE,
  enddate DATE
);

INSERT INTO  tbl  VALUES
  (1,'robert','2024-01-01','2024-01-31'),
  (2,'ann'   ,'2024-01-13','2024-01-20'),
  (3,'ken'   ,'2024-01-20','2024-01-25'),
  (4,'marco' ,'2024-01-20','2024-01-30'),
  (5,'test'  ,'2024-01-08','2024-01-10'), -- no Sundays in range
  (6,'test2' ,'2024-01-14','2024-01-21'), -- starts on one Sunday and ends on next
  (7,'test3' ,'2024-01-07','2024-01-07'), -- starts and ends on same Sunday
  (8,'test4' ,'2024-01-06','2024-01-07'), -- starts on Saturday and ends following day
  (9,'test5' ,'2024-01-07','2024-01-08'); -- starts on Sunday and ends following day

Records: 9  Duplicates: 0  Warnings: 0


WITH RECURSIVE cte AS (
    SELECT id, name, startdate, enddate,
        startdate + INTERVAL IF(DAYOFWEEK(startdate) = 1, 0, 8 - DAYOFWEEK(startdate)) DAY AS sun_date
    FROM tbl

    UNION ALL
  
    SELECT id, name, startdate, enddate,
        sun_date + INTERVAL 1 WEEK AS sun_date
    FROM cte
    WHERE sun_date + INTERVAL 1 WEEK <= enddate
)
SELECT name AS Name, sun_date AS Sundays
FROM cte
WHERE sun_date BETWEEN startdate AND enddate
ORDER BY startdate, sun_date;


Name
Sundays


robert
2024-01-07

robert
2024-01-14

robert
2024-01-21

robert
2024-01-28

test4
2024-01-07

test3
2024-01-07

test5
2024-01-07

ann
2024-01-14

test2
2024-01-14

test2
2024-01-21

ken
2024-01-21

marco
2024-01-21

marco
2024-01-28


WITH RECURSIVE cte (Sundays, max_end) AS (
    SELECT
        MIN(startdate) + INTERVAL IF(DAYOFWEEK(MIN(startdate)) = 1, 0, 8 - DAYOFWEEK(MIN(startdate))) DAY,
        MAX(enddate)
    FROM tbl

    UNION ALL
  
    SELECT Sundays + INTERVAL 1 WEEK, max_end
    FROM cte
    WHERE Sundays + INTERVAL 1 WEEK <= max_end
)
SELECT tbl.name AS Name, cte.Sundays
FROM cte
JOIN tbl ON cte.Sundays BETWEEN tbl.startdate AND tbl.enddate
ORDER BY tbl.startdate, cte.Sundays;


Name
Sundays


robert
2024-01-07

robert
2024-01-14

robert
2024-01-21

robert
2024-01-28

test4
2024-01-07

test3
2024-01-07

test5
2024-01-07

ann
2024-01-14

test2
2024-01-14

test2
2024-01-21

ken
2024-01-21

marco
2024-01-21

marco
2024-01-28

Name	Sundays
robert	2024-01-07
robert	2024-01-14
robert	2024-01-21
robert	2024-01-28
test4	2024-01-07
test3	2024-01-07
test5	2024-01-07
ann	2024-01-14
test2	2024-01-14
test2	2024-01-21
ken	2024-01-21
marco	2024-01-21
marco	2024-01-28