Count business days between two dates

from the Artful Common Queries page


The simplest support for counting business days between any two dates is a calendar table calendar(d date,isholiday bool) populated for all days in all possibly relevant years. Then the following query gives the inclusive number of business days between dates dStart and dStop:

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN dStart AND dStop 
  AND DAYOFWEEK(d) NOT IN(1,7)
  AND isholiday=0;

If that solution is not available, you have to do with a weekday count, which this function (corrected 6 Jul 2009) computes:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS BizDaysInclusive;
DELIMITER |                                     
CREATE FUNCTION BizDaysInclusive( d1 DATE, d2 DATE )                           
RETURNS INT                                                                         
DETERMINISTIC                                                                          
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;
END;
|
DELIMITER ; 
SELECT BizDaysInclusive('2009-8-1','2009-9-15');
+------------------------------------------+
| BizDaysInclusive('2009-8-1','2009-9-15') |
+------------------------------------------+
|                                       32 |
+------------------------------------------+

So does the following algorithm, posted by Elowie Cruz on a MySQL forum; for ease of use we encapsulate it in a stored procedure:

DROP PROCEDURE IF EXISTS daycount;
CREATE PROCEDURE DayCount( d1 DATE, d2 DATE )
SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays
FROM (
  SELECT
    dd.iDiff,
    ((dd.iWeeks * 2) + 
    IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) + 
    IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
  FROM (
    SELECT
      dd.iDiff,
      FLOOR(dd.iDiff / 7) AS iWeeks,
      dd.iDiff % 7 iDays,
      5 - dd.iStartDay AS iSatDiff,
      6 - dd.iStartDay AS iSunDiff
    FROM (
      SELECT
        1 + DATEDIFF(d2, d1) AS iDiff,
        WEEKDAY(d1) AS iStartDay
      ) AS dd
  ) AS dd
) AS dd ;
CALL DayCount( '2009-8-1','2009-9-15');
+-------+-----------+--------------+
| iDiff | iWorkDays | iWeekEndDays |
+-------+-----------+--------------+
|    46 |        32 |           14 |
+-------+-----------+--------------+


Return to the Artful Common Queries page