How to generate periods of specific length between two dates

Let suppose you have to generate start and end period (as DateTime) of 14 days of lenght. You have to fill all the dates between 2021-01-01 and 2021-04-20.

You can use the following query to generate the correct result:

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

You can find an interactive version of this example following this link .