Split dates into batches by batch size

split_dates_into_batches.sql

DECLARE @startDate DATE, @endDate DATE, @batchSize INT

SET @startDate = '2022-05-01'
SET @endDate = '2022-05-31'
SET @batchSize=4

;WITH dates AS (
	SELECT @startDate as batchStartDate, DATEADD(DAY, @batchSize, @startDate) batchEndDate
	UNION ALL
	SELECT DATEADD(DAY, 1, batchEndDate) batchStartDate, DATEADD(DAY, 1 + @batchSize, batchEndDate) batchEndDate
	FROM dates
	WHERE batchEndDate<@endDate
)
SELECT 
	batchStartDate,
	CASE WHEN batchEndDate>@endDate THEN @endDate ELSE batchEndDate END batchEndDate
FROM dates