SQL Server 2014

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

CREATE TABLE table1 (
  ID INTEGER,
  StartDate DATE,
  EndDate DATE
);

INSERT INTO table1
  (ID, StartDate, EndDate)
VALUES
  ('1', '20210420', '2021-05-07'),
  ('2', '20210605', '20210606'),
  ('3', '20210501', '20210630'),
  ('4', '20210520', '20210530'),
  ('5', '20210429', '20210430'),
  ('6', '20210429', '20210429'),
  ('7', '20210505', '20210506'),
  ('8', '20210507', '20210508'),
  ('9', '20210510', '20210512'),
  ('10', '20210509', '20210510');
10 rows affected

SELECT DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 14, 0) AS NextDay


NextDay


2021-05-10 00:00:00.000


SELECT DATEADD(DAY, (DATEDIFF(DAY, 6, GETDATE()) / 7) * 7 + 14, 6) AS NextDay


NextDay


2021-05-09 00:00:00.000


SELECT ID, StartDate, EndDate FROM table1 
WHERE StartDate >= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0) 
AND (EndDate <= DATEADD(DAY, (DATEDIFF(DAY, 6, GETDATE()) / 7) * 7 + 14, 6) 
    OR EndDate <= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 14, 0))


ID
StartDate
EndDate


7
2021-05-05
2021-05-06

8
2021-05-07
2021-05-08

10
2021-05-09
2021-05-10

ID	StartDate	EndDate
7	2021-05-05	2021-05-06
8	2021-05-07	2021-05-08
10	2021-05-09	2021-05-10