How to select date between dates...

Basically what I want to do is to be able to select all ofthe dates between 2 dates.

So say that I have 1 record each on March 23, March 24, March 25

but I select  dates between March 1 -March 25


I would like to get 25 results back, naturally there would be empty results on dates with no information.. So in my case above I would have 22 empty results and only 3 results with data in them.  All results would have their date on them however.


Does this make anysense?

An idea I have tossed around:
Using PHP to create a temporary table of dates, and joining that table on my query.


I figured it migh tbe possible to have mysql select all the dates though.. so I was holding off that doing the above solution.


 






LVL 1
aventureAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
Sadly, you can't get this very easily.  You need to create a table with the 25 dates in them (or at least the numbers 1-25, which you can convert into dates using ADDDATE()) do a left join to the table.  You have the right idea (in your php code).  The alternative to creating a table with the dates is to use the numbers with ADDDATE().  This migth be better if you want to always search within a single month, for example.
todd_farmerCommented:
Here's an example of how to do it with a table of numeric values.  I prefer this mechanism because you can reuse it for any date range (as long as the range isn't greater than the number of rows in the table):

CREATE TABLE DA (i INT);
INSERT INTO DA VALUES (1);
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA VALUES (0);
DELETE FROM DA WHERE i > 31;
SELECT ADDDATE('2006-03-01', i) FROM DA WHERE ADDDATE('2006-03-01', i) <= CURDATE() ORDER BY 1;
todd_farmerCommented:
Here it is running.  Note that because the DA table can be reused to get any monthly date range, you don't have to use it in the context of a temporary table - just create and populate it once.

mysql> CREATE TABLE DA (i INT);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO DA VALUES (1);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 16 rows affected (0.06 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA VALUES (0);
Query OK, 1 row affected (0.05 sec)

mysql> DELETE FROM DA WHERE i > 31;
Query OK, 1 row affected (0.05 sec)

mysql> SELECT ADDDATE('2006-03-01', i) FROM DA WHERE ADDDATE('2006-03-01', i) <= CURDATE() ORDER BY 1;
+--------------------------+
| ADDDATE('2006-03-01', i) |
+--------------------------+
| 2006-03-01               |
| 2006-03-02               |
| 2006-03-03               |
| 2006-03-04               |
| 2006-03-05               |
| 2006-03-06               |
| 2006-03-07               |
| 2006-03-08               |
| 2006-03-09               |
| 2006-03-10               |
| 2006-03-11               |
| 2006-03-12               |
| 2006-03-13               |
| 2006-03-14               |
| 2006-03-15               |
| 2006-03-16               |
| 2006-03-17               |
| 2006-03-18               |
| 2006-03-19               |
| 2006-03-20               |
| 2006-03-21               |
| 2006-03-22               |
| 2006-03-23               |
| 2006-03-24               |
| 2006-03-25               |
+--------------------------+
25 rows in set (0.00 sec)

mysql>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
todd_farmerCommented:
Thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.