How to get list of dates between two dates in mysql select query [duplicate]

42
votes

I want list of dates lies between two dates by select query. For example:

If i give '2012-02-10' and '2012-02-15' I need the result.

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

How can i get?

6
mysqlsql
Both answers below are correct, but it seems that's not what you are looking for. Can you give more information? - James
I just want list of dates between two dates. that dates are not in my table i just asking is there any function like we can get current date by SELECT CURRENT_DATE()(No need to specific table). Or We can have any other ways to get dates between two dates. @Mark Byers ans is correct but have to add one more table with large amount of data. Tats y looking some other solution if it is. - Sangeetha Krishnan
@SangeethaKrishnan I'm not sure doing it in MySQL/SQL is the best answer. Are you writing this for use directly in the database, or for a program using another language like PHP/C#, etc? - James
tsabz's answer below should be what you need if you have to do it in the database. - James

6 Answers

132
votes

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'

-for date ranges up to nearly 300 years in the future.

[Corrected following a suggested edit by UrvishAtSynapse.]

23
votes
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 your result set exactly as prescribed. This query only requires you change the two different dates in datediff and adddate.

14
votes

The accepted answer didn't work for me in MySQL 5.5. I updated the query 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'
1
votes

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

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

Then query that table as follows:

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

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

Check the stored procedure that Ron Savage did, this seems to correspond to what you need !

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