SQL Server 2022

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

CREATE TABLE Contracts ( Contract INT, Start_date DATE, End_date DATE)
INSERT Contracts
VALUES
    (1, '8/1/2022', '7/31/2024'),
    (23, '8/7/2022', '8/8/2023'),
    (26, '6/8/2022', '6/9/2025')

-- Upper bound on number of years
DECLARE @MaxYears INT = (
    SELECT 1 + MAX(DATEDIFF(year, Start_date, End_date))
    FROM COntracts
)

;WITH NumTable AS (
    SELECT 0 AS N
    UNION ALL
    SELECT N + 1
    FROM NumTable
    WHERE N < @Maxyears
)
SELECT
    C.Contract,
    D.Start_Date,
    CASE WHEN D.End_date < C.End_date THEN D.End_date ELSE C.End_date END AS End_date
FROM Contracts C
CROSS APPLY (
    SELECT
        DATEADD(year, N.N, C.Start_date) AS Start_date,
        DATEADD(day, -1, DATEADD(year, N.N + 1, C.Start_date)) AS End_date
    FROM NumTable N
) D
WHERE D.Start_date <= C.End_date
ORDER BY C.Contract, D.Start_Date

-- Simplified SQL Server 2022 version
SELECT C.Contract, D.Start_Date, LEAST(D.End_date, C.End_date) AS End_date
FROM Contracts C
CROSS APPLY (
    SELECT
        DATEADD(year, S.Value, C.Start_date) AS Start_date,
        DATEADD(day, -1, DATEADD(year, S.Value + 1, C.Start_date)) AS End_date
    FROM GENERATE_SERIES(0, DATEDIFF(year, C.Start_date, C.End_date)) S
) D
WHERE D.Start_date <= C.End_date
ORDER BY C.Contract, D.Start_Date

Contract	Start_Date	End_date
1	2022-08-01	2023-07-31
1	2023-08-01	2024-07-31
23	2022-08-07	2023-08-06
23	2023-08-07	2023-08-08
26	2022-06-08	2023-06-07
26	2023-06-08	2024-06-07
26	2024-06-08	2025-06-07
26	2025-06-08	2025-06-09

Contract	Start_Date	End_date
1	2022-08-01	2023-07-31
1	2023-08-01	2024-07-31
23	2022-08-07	2023-08-06
23	2023-08-07	2023-08-08
26	2022-06-08	2023-06-07
26	2023-06-08	2024-06-07
26	2024-06-08	2025-06-07
26	2025-06-08	2025-06-09