MySQL show all date from entire a month

Question

how can i show all date from entire a month, where month is parameter for query (for example month between 1 and 3) then output this query will show date from first date in january until last date on march. maybe preview like below.

2013-01-01
2013-01-02
2013-01-03
..
..
2013-02-28
..
2013-03-31

have any idea for this?thanks for your help

Answer 1

If you already have a table with number of entries greater than the number of dates that you need, then you can use this:

SELECT adddate('2013-01-01', @rownum := @rownum + 1) dt FROM my_table
JOIN (SELECT @rownum := -1) r
LIMIT 31;

Choose a small table for my_table. If my_table contains too many entries, use limit to make it faster.

The basis of this is using a counter like rownum that autoincrements itself. A more complex query with filtering using date functions:

SELECT * FROM 
    (SELECT adddate('2013-01-01', @rownum := @rownum + 1) dt FROM my_table
    JOIN (SELECT @rownum := -1) r LIMIT 1000) temp
WHERE MONTH(dt) = 1 AND YEAR(dt) = 2015;