how to generate dates from an specific date with an interval of 7 days each with sql

Question

I have a database of weekly reports and I need to generate dates starting from this specific date '01-01-2022' using SQL

This is the list of dates I need:

01-01-2022

08-01-2022

15-01-2022

22-01-2022

29-01-2022

05-02-2022

I tried to generate dates using this query:

SELECT DATEADD(day, 7, myDate) AS DateAdd, myDate
FROM table 
where myDate between '2022-01-1'and GETDATE()

Answer 1

Perhaps what you actually want is a Tally?

DECLARE @StartDate date = '20220101',
        @EndDate date = '20220205';
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) / 7)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows
SELECT DATEADD(DAY, T.I*7,@StartDate)
FROM Tally T;

Such a solution will be faster than a recursive Common Table Expression, especially if you need large date ranges.

Answer 2

For a small rowset you could use a recursive CTE to generate your date intervals to which you can then join:

with d as (
    select 0 interval
    union all
    select interval + 7 from d
    where interval < 35 /* days in range */
)
select DateAdd(day, interval, '20220101')
from d;