1
insert into rfdate (date)
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_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 t1 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 t2 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 t3 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 t4 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
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday')

Above code doesn't work.

Code below gathers the needed date irregardless of where database you are in since it's just date. You can try it too even without using a table with the columns presented.

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_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 t1 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 t2 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 t3 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 t4 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) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');
0
0

PROPOSED QUERY

insert into rfdate (date)
select selected_date from
(
    select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_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 t1 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 t2 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 t3 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 t4 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
) A where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

PROOF THAT IT WORKS

mysql> DROP DATABASE jeffvalerio;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE jeffvalerio;
Query OK, 1 row affected (0.00 sec)

mysql> USE jeffvalerio
Database changed
mysql> CREATE TABLE rfdate
    -> (
    ->     id int not null auto_increment,
    ->     date date,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rfdate (date)
    -> select selected_date from
    -> (
    ->     select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_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 t1 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 t2 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 t3 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 t4 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
    -> ) A where selected_date between '2015-02-01' and '2015-02-28'
    -> and dayname(selected_date) in ('Monday', 'Tuesday');
Query OK, 8 rows affected (0.09 sec)
Records: 8  Duplicates: 0  Warnings: 0

Here is the table's contents

mysql> select * from rfdate;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2015-02-02 |
|  2 | 2015-02-03 |
|  3 | 2015-02-09 |
|  4 | 2015-02-10 |
|  5 | 2015-02-16 |
|  6 | 2015-02-17 |
|  7 | 2015-02-23 |
|  8 | 2015-02-24 |
+----+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

2
  • "#1054 - Unknown column 'selected_date' in 'where clause' " That's the output error. :( Feb 5 '15 at 7:00
  • Just fixed it... Feb 5 '15 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.