How to Calculate Business Days Between Dates in MySQL Excluding Weekends?

Calculating Business Days Between Dates in MySQL Excluding Weekends

To ascertain the difference in days between two dates while excluding weekends, we employ MySQL's WEEKDAY() function. This function determines the day of the week for a given date, the week starting with Sunday. Based on this, we can devise a custom function that accommodates our specific requirement.

Creating the TOTAL_WEEKDAYS() Function:

We establish a function named TOTAL_WEEKDAYS() that accepts two DATE parameters, date1 and date2. This function meticulously computes the difference between weekdays, factoring in weekend days and edge cases:

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1  -- Total days
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),  -- Weekdays between
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)  -- Account for edge case
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);  -- Account for edge case

Usage and Testing:

To utilize this function, we provide two DATE variables, date1 and date2, for which we wish to find the business days difference. The output will exclude weekends:

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') AS weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') AS weekdays2;

Result:

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

In this example, with a start date of '2013-08-03' and an end date of '2013-08-21', there are 13 business days. The function accurately excludes the weekend days in its calculation.

The above is the detailed content of How to Calculate Business Days Between Dates in MySQL Excluding Weekends?. For more information, please follow other related articles on the PHP Chinese website!