MySQL function to find the number of working days

Question

Excel has NETWORKDAYS() function that find the number of business days between two dates.

Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays.

Answer 1

MYSQL Function returning business days between 2 dates (inclusive). The BETWEEN 2 AND 6 is Monday-Friday, this can be adjusted based on your calendar /region.

---

-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetBusinessDaysBetweenDates`(d1 DATE, d2 DATE) RETURNS int(11)
BEGIN
    DECLARE bDaysInPeriod INT;

    SET bDaysInPeriod=0;
    WHILE d1<=d2 DO
        IF DAYOFWEEK(d1) BETWEEN 2 AND 6 THEN
            SET bDaysInPeriod=bDaysInPeriod+1;
        END IF;

        SET d1=d1+INTERVAL 1 day;
    END WHILE;

    RETURN bDaysInPeriod;
END

Answer 2

I know this is an old thread, but was thinking that my solution might be helpful for some people. this is a query that I did to find the biz days without the need of functions. you can name the fields what you want, I just left them blank on purpose.

SELECT

   @tmp_s   := ept.`date_start`,
   @tmp_e   := IF(ept.`date_end` IS NULL, NOW(),ept.`date_end`),
   @start   := IF(DAYOFWEEK(@tmp_s)=1,@tmp_s + INTERVAL 1 DAY,(IF(DAYOFWEEK(@tmp_s)=7,@tmp_s + INTERVAL 2 DAY,@tmp_s)),
   @end     := IF(DAYOFWEEK(@tmp_e)=1,@tmp_e - INTERVAL 2 DAY,(IF(DAYOFWEEK(@tmp_e)=7,@tmp_e - INTERVAL 1 DAY,@tmp_e)),
   @bizdays := CASE
                  WHEN DATEDIFF(@end,@start)>7 THEN CEIL((DATEDIFF(@end,@start)/7)*5)
                  WHEN DAYOFWEEK(@end)< DAYOFWEEK(@start) THEN DATEDIFF(@end,@start)-2
                  ELSE DATEDIFF(@end,@start)
               END,
   DATE(@start),
   DATE(@end),
   IF(@bizdays>=10,10,@bizdays)

FROM `employee_points` ept
WHERE ept.`date_start` > '2011-01-01'

Answer 3

I needed two functions. One to calculate the number of business days between two dates and one to add/subtract x business days to a date. Here is what I put together from examples I found on the internet. They are made to be close to the standard DATEDIFF() and DATE_ADD() functions as well as compliment each others' calculations. For example, DateDiffBusiness('2014-05-14', DateAddBusiness('2014-05-14',5)) will equal 5.

DROP FUNCTION IF EXISTS DateDiffBusiness;
DELIMITER &
CREATE FUNCTION DateDiffBusiness( d2 DATE, d1 DATE )
RETURNS INT
DETERMINISTIC
COMMENT 'Calculates the number of bussiness days between two dates'
BEGIN
  DECLARE dow1, dow2, days INT;
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(d2);
  SET days = FLOOR( DATEDIFF(d2,d1)/7 ) * 5 +
             CASE
               WHEN dow1=1 AND dow2=7 THEN 5
               WHEN dow1 IN(7,1) AND dow2 IN (7,1) THEN 0
               WHEN dow1=dow2 THEN 1
               WHEN dow1 IN(7,1) AND dow2 NOT IN (7,1) THEN dow2-1
               WHEN dow1 NOT IN(7,1) AND dow2 IN(7,1) THEN 7-dow1
               WHEN dow1<=dow2 THEN dow2-dow1+1
               WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
               ELSE 0
             END;
  RETURN days-1;
END&
DELIMITER ;


DROP FUNCTION IF EXISTS DateAddBusiness;
DELIMITER &
CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT) 
RETURNS DATE
DETERMINISTIC
COMMENT 'Adds bussiness days between two dates'
BEGIN
 DECLARE num_week INT DEFAULT 0;
 DECLARE num_day INT DEFAULT 0;
 DECLARE adj INT DEFAULT 0;
 DECLARE total INT DEFAULT 0;
 SET num_week = numday DIV 5;
 SET num_day = MOD(numday, 5);
 IF (WEEKDAY(mydate) + num_day >= 5) then
  SET adj = 2;
 END IF;
 SET total = num_week * 7 + adj + num_day;
 RETURN DATE_ADD(mydate, INTERVAL total DAY);
END&
DELIMITER ;

Answer 4

I added a stored procedure in my MySQL DB to count the total working days of my team (I called it WORKDAYS):

RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7)
     - (SELECT DISTINCT COUNT(PriKey) FROM holidays WHERE date BETWEEN date1 AND date2)
     + (SELECT DISTINCT COUNT(PriKey) FROM weekenddaysworked WHERE date BETWEEN date1 AND date2)

I added two tables to my DB: holidays and weekenddaysworked both with two columns (PriKey (int, 11), data (date))

In holidays I added the holidays I needed to be taken into account and in weekenddaysworked I added dates where my guys worked on the weekend.

I added the procedure as a function with an INT as result. date1 and date2 are defined as DATE.

Now I can call the MySQL function like so:

WORKDAYS(date1,date2) - so for example WORKDAYS('2018-11-01','2018-12-01')

Answer 5

This solution uses basically the same approach as Rodger's except the method for generating the matrix is much more complex. Note: This output of this solution is not compatible with NETWORKDAYS.

As in Rodger's solution, this calculates the number of business days between the start date (@S) and the end date (@E) without having to define a stored procedure. It assumes that the end date is not before the start date. Using the same start and end date will produce 0. Holidays are not taken into account.

The major difference between this and Rodger's solution is that the matrix and resulting string of digits is constructed by a complex algorithm which I have not included. The output of this algorithm is validated by a unit test (see the test inputs and outputs below). In the matrix, the intersection of any given x and y value pair (WEEKDAY(@S) and WEEKDAY(@E) yields the difference in work days between the two values. The assignment order is actually unimportant as the two are added together to plot the position.

Business days are Monday-Friday

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

The 49 values in the table are concatenated into the following string:

0123455501234445012333450122234501101234000123450

In the end, the correct expression is:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

I have verified the following inputs and outputs using this solution:

Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5

Answer 6

SELECT  5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)

This is when you want to consider the following cases:

1) if startdate = enddate, duration = 1 and likewise..

I calculated the string using the logic mentioned in the most voted answer and got results as I needed.