In case it helps someone, this is is a recursive CTE solution that I needed recently:

DECLARE @PayCalendarStartDate DATE = '2024-01-01'; DECLARE @PayCalendarEndDate DATE = '2024-12-31';

WITH PayCalendar AS ( SELECT PayPeriodStartDate = @PayCalendarStartDate , PayPeriodEndDate = DATEADD (DAY, 13, @PayCalendarStartDate) UNION ALL SELECT PayPeriodStartDate = DATEADD (DAY, 14, PayCalendar.PayPeriodStartDate) , PayPeriodEndDate = DATEADD (DAY, 14, PayCalendar.PayPeriodEndDate) FROM PayCalendar WHERE PayPeriodEndDate <= @PayCalendarEndDate ) SELECT * FROM PayCalendar;