select a.Date
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
where a.Date between '2010-01-20' and '2010-01-24'
meanwhile, you could also just create a numbers table that is large enough to cover the largest date range that you want to handle…
CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),...
then that query simplifies to –
SELECT DATE('2011-02-01') -- start date
+ INTERVAL n DAY AS thedate
from numbers
WHERE DATE('2011-02-01') + INTERVAL n DAY
< DATE('2011-03-01') -- end date plus one
I like the union-all method of creating “temporary tables”.
The following should work:
select x.Date
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
) x
where x.Date between '2010-01-20' and '2010-01-24'
because it’s very slow (converting a date into a string), and furthermore, leaves the result as a string, which, if you want to use it for anything (such as displaying it as a formatted datetime) has to first be converted into a datetime before the date formatting function converts it back into a string
ewwww :injured:
and besides, using CURRENT_DATE instead of LAST_DAY(CURENT_DATE) puts you well outside of the required last day of the month on approximately 30 out of 31 days
I think it does have some limitation, for example if we want to find dates between 2010-01-20 and 2010-05-20, we will not get whole dates between them. There is some limit of 35 or 50 for that query. Otherwise we have to rewrite query a bit.