How do I add the first of the week for every week from 2011-01-02 until 2100-01-01 to a mysql table via a while loop?

Question

I'm looking to make a date lookup table that will allow me to match some pivots together in excel and I was wondering how to go about taking the first of every week and adding it to a new mysql table?

First of week being being Monday or Sunday? Or what do you mean? — Hyperboreus, Jul 07 '11 at 18:46

score 1 · Accepted Answer · edited May 23 '17 at 12:12

This query selects all sundays of 2011. Expand it for the years you need and insert this select into a table of your choice.

select a.Date 
from (
    select DATE('2011-12-31') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where DAYOFWEEK(a.Date)=1 and a.Date between '2011-01-01' and '2011-12-31'

Reference

How do I add the first of the week for every week from 2011-01-02 until 2100-01-01 to a mysql table via a while loop?

1 Answers1