Count business days between two dates
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 2024) 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('2024-8-1','2024-9-15');
+------------------------------------------+
| BizDaysInclusive('2024-8-1','2024-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( '2024-8-1','2024-9-15');
+-------+-----------+--------------+
| iDiff | iWorkDays | iWeekEndDays |
+-------+-----------+--------------+
| 46 | 32 | 14 |
+-------+-----------+--------------+