How can I get a list of dates between two given dates with mysql?

Question

I need to generate a list of dates with MySQL between two given dates. This list of dates must be filtered to the dates that correspond to Tuesday and Thursday. Is it possible?

Answer 1

A really easy way to do it is to have a table of numbers. Query this table for numbers less than the number of days between the two dates, and select the smaller date plus the number to return the dates themselves. Easy to stick a predicate in the where clause to handle Tuesdays and Thursdays too.

Answer 2

Here is a stored function called MakeDateList:

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`MakeDateList` $$
CREATE FUNCTION `junk`.`MakeDateList`
(
  BeginDate DATE,
  EndDate DATE,
  InclusionList VARCHAR(20)
)
RETURNS VARCHAR(4096)
DETERMINISTIC
BEGIN
  DECLARE RunningDate DATE;
  DECLARE rv VARCHAR(4096);
  DECLARE comma CHAR(1);
  DECLARE IncList,DOWValue VARCHAR(20);
  DECLARE OK_To_Add INT;

  SET IncList = CONCAT(',',InclusionList,',');
  SET comma = '';
  SET rv = '';
  SET RunningDate = BeginDate;
  WHILE RunningDate <= EndDate DO
    SET OK_To_Add = 0;
    SET DOWValue = CONCAT(',',DAYOFWEEK(RunningDate),',');
    IF LOCATE(DOWValue,IncList) > 0 THEN
      SET OK_To_Add = 1;
    END IF;
    IF OK_To_Add = 1 THEN
      SET rv = CONCAT(rv,comma,RunningDate);
    END IF;
    SET comma = ',';
    SET RunningDate = RunningDate + INTERVAL 1 DAY;
  END WHILE;
  RETURN rv;
END $$
DELIMITER ;

It has three parameters: BeginDate, EndDate, and InclusionList

InclusionList is a comma speararted list of numbers (valid numbers 1-7)

• 1 = Sunday
• 2 = Monday
• 3 = Tuesday
• 4 = Wednesday
• 5 = Thursday
• 6 = Friday
• 7 = Saturday

So to get Tuesdays and Thursdays, make the InclusionList 3,5

Example : Calling MakeDateList('2011-10-01','2011-10-01','3,5') should give you Tuesdays and Thursdays for the whole month of October 2011

Here is the loading of the stored function:

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `junk`.`MakeDateList` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION `junk`.`MakeDateList`
    -> (
    ->   BeginDate DATE,
    ->   EndDate DATE,
    ->   InclusionList VARCHAR(20)
    -> )
    -> RETURNS VARCHAR(4096)
    -> DETERMINISTIC
    -> BEGIN
    ->   DECLARE RunningDate DATE;
    ->   DECLARE rv VARCHAR(4096);
    ->   DECLARE comma CHAR(1);
    ->   DECLARE IncList,DOWValue VARCHAR(20);
    ->   DECLARE OK_To_Add INT;
    ->
    ->   SET IncList = CONCAT(',',InclusionList,',');
    ->   SET comma = '';
    ->   SET rv = '';
    ->   SET RunningDate = BeginDate;
    ->   WHILE RunningDate <= EndDate DO
    ->     SET OK_To_Add = 0;
    ->     SET DOWValue = CONCAT(',',DAYOFWEEK(RunningDate),',');
    ->     IF LOCATE(DOWValue,IncList) > 0 THEN
    ->       SET OK_To_Add = 1;
    ->     END IF;
    ->     IF OK_To_Add = 1 THEN
    ->       SET rv = CONCAT(rv,comma,RunningDate);
    ->       SET comma = ',';
    ->     END IF;
    ->     SET RunningDate = RunningDate + INTERVAL 1 DAY;
    ->   END WHILE;
    ->   RETURN rv;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Here is the result of the example:

mysql> SELECT MakeDateList('2011-10-01','2011-10-31','3,5');
+-----------------------------------------------------------------------------------------+
| MakeDateList('2011-10-01','2011-10-31','3,5')                                           |
+-----------------------------------------------------------------------------------------+
| 2011-10-04,2011-10-06,2011-10-11,2011-10-13,2011-10-18,2011-10-20,2011-10-25,2011-10-27 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!