MySql - How to get working days?

Question

I want to get the working days of the week between two dates and output them into a table like this:

|2014-07-21|Monday   |
|2014-07-22|Tuesday  |
|2014-07-23|Wednesday|
|2014-07-24|Thursday |
|2014-07-25|Friday   |

This is what i tried so far as a result of what i found on internet.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `interval_between`(startdate Date, enddate Date, interval_size VARCHAR(10), interval_value INT)
BEGIN
    DECLARE thisDate Date;
    SET thisDate = startdate;
    CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals (       
        interval_from Date      
    );

    DELETE FROM time_intervals;

    REPEAT
        INSERT INTO time_intervals SELECT  thisDate;
        SELECT
            CASE interval_size
                WHEN 'MICROSECOND' THEN TIMESTAMPadd(MICROSECOND, interval_value, thisDate)
                WHEN 'SECOND'      THEN TIMESTAMPadd(SECOND, interval_value, thisDate)
                WHEN 'MINUTE'      THEN TIMESTAMPadd(MINUTE, interval_value, thisDate)
                WHEN 'HOUR'        THEN TIMESTAMPadd(HOUR, interval_value, thisDate)
                WHEN 'DAY'         THEN TIMESTAMPadd(DAY, interval_value, thisDate)
                WHEN 'WEEK'        THEN TIMESTAMPadd(WEEK, interval_value, thisDate)
                WHEN 'MONTH'       THEN TIMESTAMPadd(MONTH, interval_value, thisDate)
                WHEN 'YEAR'        THEN TIMESTAMPadd(YEAR, interval_value, thisDate)
            END INTO thisDate;
    UNTIL thisDate >= enddate
    END REPEAT; 
END

...and let us know, what output you are getting from given code, and what is the problem with that? — Ravi Dhoriya ツ, Jul 23 '14 at 10:57
[this question](http://stackoverflow.com/questions/2157282/generate-days-from-date-range) seems relevant to your problem. — Vatev, Jul 23 '14 at 10:58
@Log1cツ from that code i get a table with the dates between 2 dates received as input. And i want to get the working days from that table. — laura, Jul 23 '14 at 11:04
Sure, but e.g. the national day is not really working-day. Thats why I ask. If you just want to calculate the working-day days without weekends, ignoring the public holidays, or are you looking for something that will also be able to include those? — t.niese, Jul 23 '14 at 11:37
@t.niese i want to calculate the working-days without weekends, ignoring the public holidays — laura, Jul 23 '14 at 11:40
Then **[mysql-function to count days between 2 dates excluding weekends](http://stackoverflow.com/questions/18302181/mysql-function-to-count-days-between-2-dates-excluding-weekends)** or the question referenced there should work for you. If not you should explain why it is not working for you. — t.niese, Jul 23 '14 at 13:10

score 0 · Answer 1 · answered Jul 23 '14 at 14:10

Assuming you want a list of days between 2 dates (inclusive) then the following will do it, with a max range of 1000 days.

This joins 3 sub queries against each other, treating them as units, tens and hundreds and from that calculates the numbers 0 to 999. This is added to the start date while the number of days is less than the difference between the 2 dates. DAYOFWEEK is then used to exclude Saturdays and Sundays.

SELECT DATE_ADD('2014-07-21', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS aDate
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
WHERE DATEDIFF('2014-08-25', '2014-07-21') >= units.i + tens.i * 10 + hundreds.i * 100
HAVING DAYOFWEEK(aDate) BETWEEN 2 AND 6

score 0 · Answer 2 · answered Jul 23 '14 at 16:18

I created a stored procedure which returns a temporary table filled with dates between 2 given dates.

DELIMITER $$    
CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS date_range (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, day DATE);

    Delete from date_range;

    WHILE dateStart <= dateEnd DO
      INSERT INTO date_range(day) VALUES (dateStart);
      SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;
END

Select only the working days (excluding weekends)

 SELECT ADDDATE('2014-07-01', (date_range.id - 1)) as Date,
 DAYOFWEEK(ADDDATE('2014-07-01',(date_range.id - 1))) as Day
 FROM date_range
 WHERE id <= DATEDIFF('2014-07-31','2014-07-01')
 AND DAYOFWEEK(ADDDATE('2014-07-01',(date_range.id - 1))) NOT IN (1,7);

The output is:

enter image description here

MySql - How to get working days?

2 Answers2