How to get a list of dates between two dates in mysql select query

Question

I want the date list to be between two dates at the request of select. For example:

If I give "2012-02-10" and "2012-02-15", I need a result.

date ---------- 2012-02-10 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 

How can I get?

Answer 1

Try:

 select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i 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 i 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 i 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 i 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 i 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 '2012-02-10' and '2012-02-15' 

- today for nearly 300 years in the future.

[Corrected according to proposed editing by UrvishAtSynapse.]

Answer 2

 set @i = -1; SELECT DATE(ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY)) AS date FROM `table` HAVING @i < DATEDIFF('2012-02-15', '2012-02-10') 

This will return the result exactly as indicated. This query requires you to change two different dates in dates and add.

Answer 3

The accepted answer did not work for me in MySQL 5.5. I updated the request to work for me:

 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 '2012-02-10' and '2012-02-15' 

Answer 4

You can create a table containing all the dates you will ever need to use:

 date 2000-01-01 2000-01-02 2000-01-03 ...etc.. 2100-12-30 2100-12-31 

Then run the following table:

 SELECT date FROM dates WHERE date BETWEEN '2012-02-10' AND '2012-02-15' 

Answer 5

Take a look at this post: Get a list of dates between two dates

Check out the stored procedure that Ron Savage does, it looks like what you need!

Answer 6

 SELECT * FROM tablexxx WHERE datecol BETWEEN '2012-02-10' AND '2012-02-15';