Link to home
Start Free TrialLog in
Avatar of aventure
aventure

asked on

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.


 






Avatar of todd_farmer
todd_farmer
Flag of United States of America image

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.
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;
ASKER CERTIFIED SOLUTION
Avatar of todd_farmer
todd_farmer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the points.