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

I need to generate a list of dates with MySQL between two given dates and also, this list of the dates must to filter the dates that corresponds to Tuesday and Thursday days. Is it possible?

thanks.

– Answer –

• 20 November 2011: Answer by RolandoMySQLDBA for How can I get a list of dates between two given dates with mysql? -

  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 !!!

• 19 November 2011: Answer by Rob Farley for How can I get a list of dates between two given dates with mysql? -

  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.

• 19 November 2011: How can I get a list of dates between two given dates with mysql? -

  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?

This entry was posted on Saturday, November 19th, 2011 at 9:44 pm and is filed under Database admin. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.