SQL - Insert dates in a field between a date range-CodePudding

I am trying to insert a date in a field every 7 days between year 2021 and 2023 This is for me not to insert it manually several times. I can't find any related post here in stack so I decided to ask since I really need this task.

Appreciate your answer or any reference that might help me through this.

EDIT: MySQL

CodePudding user response：

You could use a blanket calendar table approach modified to generate dates in increments of 7 days, something like this:

SELECT *
FROM
(
    SELECT ADDDATE('1970-01-01', 7*(t4*10000   t3*1000   t2*100   t1*10   t0)) gen_date
    FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) t
WHERE gen_date >= '2021-01-01' and gen_date < '2024-01-01';

Demo

CodePudding user response：

declare @startDate date
declare @endDate date
set @startDate='2021-03-01' -- YYYY-MM-DD format
set @endDate='2023-04-01'

;with all_date as (
    select @startDate Dates
    union all
    select DATEADD(day, 7, Dates)
    from all_date
    where Dates < @endDate
)
INSERT INTO [Table_name]([column])
select Dates from all_date

You can use like this ...