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

Question

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?

Both answers below are correct, but it seems that's not what you are looking for. Can you give more information? — James
– James, Commented Feb 15, 2012 at 15:07
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
– Sangeetha Krishnan, Commented Feb 15, 2012 at 15:36
@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
– James, Commented Feb 15, 2012 at 16:02
tsabz's answer below should be what you need if you have to do it in the database. — James
– James, Commented Feb 15, 2012 at 16:07

score 158 · Accepted Answer · 2012-12-22 15:01:36Z

158

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.]

edited Dec 22, 2012 at 15:01

answered Feb 15, 2012 at 15:32

user359040

Sign up to request clarification or add additional context in comments.

7 Comments

s2t2 Over a year ago

out of all the answers to this question, and all the answers to the related questions, this solution is the simplest and best. no need for stored procedures. just make a table from this output. nice job @MarkBannister

Rahul Prasad Over a year ago

I would be thankful if you can explain the query

Gerard ONeill Over a year ago

Rahul, this generates 100000 rows by joining a combination of each digit together (00000, 00001, ... 99999). Each row's columns is converted into a single number, which is added to the date '1970-01-01', which gives you 100000 dates, one per row, which are then filtered down. efficiency can be gained by starting from the day you want (not 1970-01-01), converting the between to a '<=', and finally reducing the order of magnitude to what you need. If you only need 20 dates, then 100, or t0 joined with t1, are all you need.

Paul Maxwell Over a year ago

perhaps a small point: but each union in select 0 i union select 1 union ... should be union all

Paul Maxwell Over a year ago

not entirely true, union all does not scan the resultset for uniqueness, union distinct does. In this case the performance difference is trivial, but as examples of best practice it does matter (imho)

|

hexparrot · Accepted Answer · 2012-02-15 15:41:00Z

23

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.

edited Feb 15, 2012 at 15:41

answered Feb 15, 2012 at 15:34

hexparrot

3,4471 gold badge27 silver badges33 bronze badges

6 Comments

hexparrot Over a year ago

I must also mention that table does not need to contain any date rows, but SQL queries simply require the FROM to be present. table can be ANY table in your db.

bonCodigo Over a year ago

I have tried to select dates bases on the dates generated using a nested query like yours. Reference sqlfiddle but for some reason, query returns nothing. Could you please let me know how you had intended to use these values generated by your query?

Ankit Sharma Over a year ago

query doesnot show me more than 4 records.. always shows 4 records in date column... i have tried changing the datediff function dates..

maks1m Over a year ago

Nice solution but there is one disappointing moment. The table you declare in select should have more or equal number of rows you wish to select. So if you want to get days for a whole year and your table only has 100 rows you will get only first 100 rows. That is why I prefer this solution stackoverflow.com/a/33411681/3358250

SteJ Over a year ago

This query only lists as many results as there are in table - this is fine for large record sets where the number of results will always be less than the number of records, but where you are trying to retrieve more dates than there are records in the table it won't work.

|

NateTallman · Accepted Answer · 2012-12-11 06:34:47Z

16

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'

answered Dec 11, 2012 at 6:34

NateTallman

1691 silver badge4 bronze badges

1 Comment

totallyNotLizards Over a year ago

this method does the job but if you find it a bit slow, and you only need future dates, replace '1970-01-01' with CURDATE() - in my case it reduced execution time by half, by reducing the number of resulting rows from the adddate call.

Community · Accepted Answer · 2017-05-23 11:54:47Z

1

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 !

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Feb 15, 2012 at 15:01

BMN

8,57614 gold badges51 silver badges80 bronze badges

3 Comments

BMN Over a year ago

Why -1 ? This procedure is a bit complex but really performant, and he can get exactly what he needs with it.

James Over a year ago

I gave it a plus one to at least bring it back up to 0. I just posted the same thread, then seen you already did. This is what they need.

hexparrot Over a year ago

Why use a stored proc when one is clearly not needed?

Mark Byers · Accepted Answer · 2012-02-15 15:04:09Z

0

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'

edited Feb 15, 2012 at 15:04

answered Feb 15, 2012 at 14:58

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

4 Comments

Sangeetha Krishnan Over a year ago

Thank you for ur reply.Is there any other way instead of store this much of data?

Mark Byers Over a year ago

@SangeethaKrishnan: It's not actually that much data if you think about it. 100 years of dates is only about 36500 records - it takes almost no storage space on your server. If you're worried about performance, make sure the column is indexed.

hexparrot Over a year ago

Best practice would dictate that you don't store computable data. Saving every date for x years seems very inelegant.

VH-NZZ Over a year ago

@hexparrot: Agreed but 'best practice' is to be understood more broadly here. If the OP wants a portable solution for dates, I really see no huge penalty in storing a sequence of dates in a separate table, despite obviously lacking in elegance but with indexing the column as a bonus. The accepted answer is also fairly portable but since MySQL does not allow subqueries in views, the query cannot be stored as such. So what would be your 'best practice' guideline here if the OP wants to join the sequence of dates in more than query? Stored procedure?

Taryn · Accepted Answer · 2012-02-15 14:59:26Z

-8

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

edited Feb 15, 2012 at 14:59

Taryn

249k57 gold badges374 silver badges409 bronze badges

answered Feb 15, 2012 at 14:58

blankabout

2,6473 gold badges20 silver badges29 bronze badges

3 Comments

Sangeetha Krishnan Over a year ago

Thank u for ur reply. Those dates are not in table.

Sangeetha Krishnan Over a year ago

I want it common. That means like select CURRENT_DATE().

blankabout Over a year ago

Personally I think anonymous downvotes, especially without explanations, should not be allowed.

Collectives™ on Stack Overflow

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

6 Answers 6

7 Comments

6 Comments

1 Comment

3 Comments

4 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

6 Comments

1 Comment

3 Comments

4 Comments

3 Comments

Linked

Related