How to Calculate Date Differences Excluding Weekends in MySQL?

Excluding Weekends in Date Difference Calculations: A MySQL Solution

To accurately calculate the difference between two dates, excluding weekends, MySQL users often seek guidance. The standard DATEDIFF function, while useful for basic date differences, falls short when weekends need to be omitted.

To address this issue, a custom function can be created using the WEEKDAY function. Here's how:

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);

This function uses several MySQL functions:

• ABS: Returns the absolute value of the number.
• DATEDIFF: Determines the difference between two dates.
• ADDDATE: Adds a specified number of days to a date.
• DAYOFWEEK: Gets the day of the week for a given date, with 1 representing Sunday and 7 representing Saturday.

By combining these functions, the TOTAL_WEEKDAYS function calculates the absolute difference between the two input dates, subtracts the number of Saturdays and Sundays in the span, and adjusts for the days at the start and end of the range.

Example Usage:

To calculate the difference between two dates, excluding 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 |

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