I want to display all dates between a from and to dates from MySQL.
For example the data from schedule table that has from and to fields are:
from date is 2013-3-13, and
to date is 2013-3-20,
my desired result is:
2013-3-13
2013-3-14
2013-3-15
2013-3-16
2013-3-17
2013-3-18
2013-3-19
2013-3-20
How can I achieve this using MySQL query only (without having to use stored procedure 'cause I'm not familiar with it)?
EDIT:
The answer here is very helpful, though I still don't fully get what is desired. In this sample, it only runs successfully but doesn't output anything. And I don't know what seems to be the problem.
Please help. Thanks!
解决方案
You can use the following to generate your list of dates:
select a.Date, s.*
from
(
select curdate() + 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
inner join schedule s
on a.Date >= s.fromDate
and a.Date <= s.toDate