SQL Server 2019

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

CREATE TABLE dbo.YourTable (ID int,
                            FromDate date,
                            ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
      (2,'20210517','20210525');
2 rows affected

DECLARE @StartDate date = '20210501',
        @EndDate date = '20210528';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
           T.I
    FROM Tally T),
Grps AS(
    SELECT D.[Date],
           YT.ID,
           D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
    FROM Dates D
         LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
       MIN(G.[Date]) AS FromDate,
       MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;


ID
FromDate
ToDate


0
2021-05-01
2021-05-04

1
2021-05-05
2021-05-10

0
2021-05-11
2021-05-16

2
2021-05-17
2021-05-25

0
2021-05-26
2021-05-28


Warning: Null value is eliminated by an aggregate or other SET operation.


DROP TABLE dbo.YourTable;

ID	FromDate	ToDate
0	2021-05-01	2021-05-04
1	2021-05-05	2021-05-10
0	2021-05-11	2021-05-16
2	2021-05-17	2021-05-25
0	2021-05-26	2021-05-28