How to Calculate the Number of Weekdays Between Two Dates in MySQL, Excluding Weekends?

Calculating Days Between Dates Excluding Weekends in MySQL

In MySQL, calculating the difference between two dates is straightforward using the DATEDIFF function. However, excluding weekends (Saturday and Sunday) from this calculation can be challenging.

To achieve this, we can utilize the WEEKDAY function and a custom function to simplify the process.

Creating a Custom Function

We can create a function called TOTAL_WEEKDAYS that accepts two date parameters (date1 and date2) and returns the number of weekdays between them:

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
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);

The function calculates the absolute difference between the two dates, adds one to include the start and end dates, and then subtracts the total number of weekends that occur within that period. The additional subtractions account for cases where the start or end date falls on a weekend.

Example Usage

To use the TOTAL_WEEKDAYS function, simply call it with the desired dates:

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

Results

WEEKDAYS1	WEEKDAYS2
13	13

As you can see, the function accurately calculates the number of weekdays between the given dates, excluding weekends.

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