52

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Sangeetha Krishnan
  • 941
  • 1
  • 12
  • 17
  • Both answers below are correct, but it seems that's not what you are looking for. Can you give more information? – James Feb 15 '12 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 Feb 15 '12 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 Feb 15 '12 at 16:02
  • tsabz's answer below should be what you need if you have to do it in the database. – James Feb 15 '12 at 16:07

6 Answers6

151

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

  • 1
    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 – s2t2 Jul 18 '13 at 23:57
  • 19
    I would be thankful if you can explain the query – Rahul Prasad Apr 16 '14 at 21:49
  • 10
    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. – Gerard ONeill Sep 29 '16 at 21:26
  • perhaps a small point: but each union in `select 0 i union select 1 union ...` should be `union all` – Paul Maxwell Oct 07 '17 at 04:55
  • @Used_By_Already: It doesn't actually make any difference *in this case* - since each of the values is distinct, `union distinct` has the same effect as `union all`. –  Oct 07 '17 at 06:28
  • 2
    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) – Paul Maxwell Oct 07 '17 at 06:45
  • Thank you this is very clever and exactly what I needed for an unusual query operation. – Uncle Long Hair Jan 08 '18 at 17:36
24
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.

hexparrot
  • 3,399
  • 1
  • 24
  • 33
  • 1
    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. – hexparrot Feb 15 '12 at 15:48
  • I have tried to select dates bases on the dates generated using a nested query like yours. Reference [sqlfiddle](http://sqlfiddle.com/#!2/16a9c) 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? – bonCodigo Dec 08 '12 at 12:30
  • 1
    query doesnot show me more than 4 records.. always shows 4 records in date column... i have tried changing the datediff function dates.. – Ankit Sharma May 22 '13 at 19:19
  • If I don't have dates but counters start and end (like time stamps), it seems not working: SELECT start + @i:=@i+1 AS counter FROM table HAVING @i < end-start. end and start are the values of each row in the table which I would like to expand with values between end and start. – giordano Sep 26 '13 at 07:40
  • 9
    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 http://stackoverflow.com/a/33411681/3358250 – maks1m Sep 15 '16 at 14:55
  • 2
    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. – SteJ Oct 11 '16 at 12:04
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'
NateTallman
  • 169
  • 1
  • 4
  • 2
    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. – totallyNotLizards May 01 '13 at 12:41
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 !

Community
  • 1
  • 1
BMN
  • 8,253
  • 14
  • 48
  • 80
  • 3
    Why -1 ? This procedure is a bit complex but really performant, and he can get exactly what he needs with it. – BMN Feb 15 '12 at 15:40
  • 1
    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. – James Feb 15 '12 at 16:06
  • 1
    Why use a stored proc when one is clearly not needed? – hexparrot Feb 15 '12 at 20:31
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'
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Thank you for ur reply.Is there any other way instead of store this much of data? – Sangeetha Krishnan Feb 15 '12 at 15:05
  • @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. – Mark Byers Feb 15 '12 at 15:14
  • 4
    Best practice would dictate that you don't store computable data. Saving every date for x years seems very inelegant. – hexparrot Feb 15 '12 at 15:18
  • 2
    @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? – VH-NZZ Jan 10 '14 at 12:28
-8
SELECT * FROM tablexxx WHERE datecol BETWEEN '2012-02-10' AND '2012-02-15';
Taryn
  • 242,637
  • 56
  • 362
  • 405
blankabout
  • 2,597
  • 2
  • 18
  • 29