3

I want to display all dates between a from and to dates from MySQL.

For example the data from schedule table that has from and to fields are:

from date is 2013-3-13, and

to date is 2013-3-20,

my desired result is:

 2013-3-13
 2013-3-14
 2013-3-15
 2013-3-16
 2013-3-17
 2013-3-18
 2013-3-19
 2013-3-20

How can I achieve this using MySQL query only (without having to use stored procedure 'cause I'm not familiar with it)?

EDIT:

The answer here is very helpful, though I still don't fully get what is desired. In this sample, it only runs successfully but doesn't output anything. And I don't know what seems to be the problem.

Please help. Thanks!

Community
  • 1
  • 1
xjshiya
  • 915
  • 7
  • 16
  • 44
  • are this dates present on the database or you want to fill the gaps between dates? – John Woo Mar 05 '13 at 11:49
  • Only dates from and to are present. The dates in between are not, so I want to fill the gaps. – xjshiya Mar 05 '13 at 11:51
  • 1
    ok, here is a great article to fill up dates [Using MySQL to generate daily sales reports with filled gaps](http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/) basically create a calendar. – John Woo Mar 05 '13 at 11:54
  • Is there a simpler way to do it? I mean without adding MySQL procedure? – xjshiya Mar 05 '13 at 11:57
  • @bluefeet, the accepted answer there is great. However, when I changed the dates to `2013-3-1` and `2013-3-13`, The output ranges only from `2013-3-1` up to `2013-3-5`. – xjshiya Mar 05 '13 at 12:15
  • @xjshiya That's because that query goes back from the current date, and you ran it on 2013-3-5. Change it to go back from the end date that you want instead. E.g., see [updated fiddle](http://sqlfiddle.com/#!2/a2581/5972). – D'Arcy Rittich Mar 05 '13 at 17:23
  • @RedFilter, what if I want to exclued a certain date to the result of that query you provided? How can I restrain the code? Thanks. – xjshiya Jul 02 '13 at 07:32

2 Answers2

8

You can use the following to generate your list of dates:

select a.Date,  s.*
from 
(
  select curdate() + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
  from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
inner join schedule s
  on a.Date >= s.fromDate 
  and a.Date <= s.toDate

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • how can I union another query with this code? `SELECT acode FROM reservation`? Thanks. – xjshiya Mar 11 '13 at 03:10
  • @xjshiya Please post a new question with the details on the new problem. It will be easier than going back and forth in the comments. :) – Taryn Mar 11 '13 at 10:33
-4

try where from date is 2013-3-13, and to date is 2013-3-20, 

"SELECT * FROM schedule WHERE Field BETWEEN $fromdate AND $todate";
Devang Rathod
  • 6,650
  • 2
  • 23
  • 32