By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @intervalDays int = 14 -- Lenght of period (in days)
DECLARE @TabDates TABLE (
ID int PRIMARY KEY identity,
StartDatetime datetime,
EndTDatetime datetime
)
DECLARE @StartDatetime DATETIME = '2021-01-01 00:00'
DECLARE @EndTDatetime DATETIME = '2021-04-20 00:00'
;WITH CTE AS
(
SELECT @StartDatetime st
UNION ALL
SELECT DATEADD(DAY,@intervalDays,st) st
FROM cte
WHERE DATEADD(DAY,@intervalDays,st) < @EndTDatetime
)
INSERT INTO @TabDates(StartDatetime,EndTDatetime)
SELECT st,DATEADD(DAY,@intervalDays,st) FROM cte
UPDATE @TabDates
SET EndTDatetime=DATEADD(second,-1, EndTDatetime)
SELECT *
FROM @TabDates
| ID | StartDatetime | EndTDatetime |
|---|---|---|
| 1 | 2021-01-01 00:00:00.000 | 2021-01-14 23:59:59.000 |
| 2 | 2021-01-15 00:00:00.000 | 2021-01-28 23:59:59.000 |
| 3 | 2021-01-29 00:00:00.000 | 2021-02-11 23:59:59.000 |
| 4 | 2021-02-12 00:00:00.000 | 2021-02-25 23:59:59.000 |
| 5 | 2021-02-26 00:00:00.000 | 2021-03-11 23:59:59.000 |
| 6 | 2021-03-12 00:00:00.000 | 2021-03-25 23:59:59.000 |
| 7 | 2021-03-26 00:00:00.000 | 2021-04-08 23:59:59.000 |
| 8 | 2021-04-09 00:00:00.000 | 2021-04-22 23:59:59.000 |