We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to select date between dates...

aventure
aventure asked
on
Medium Priority
943 Views
Last Modified: 2008-03-10
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.


 






Comment
Watch Question

Top Expert 2006

Commented:
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.
Top Expert 2006

Commented:
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;
Top Expert 2006
Commented:
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>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2006

Commented:
Thanks for the points.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.