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

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()

CodePudding user response：

Just sharing another solutions.

declare @date_start Datetime  --declare variable for starting dates
set @date_start  = '2022-01-01' --starts from 

;with Weekdays (Date)
AS
(
    select @date_start
    union all
    select Weekdays.Date  7 from Weekdays where Weekdays.Date < GETDATE()
)
select * from Weekdays

CodePudding user response：

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.

CodePudding user response：

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;