## 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 | +-------+-----------+--------------+