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:
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!