Home > Database > Mysql Tutorial > body text

How to Calculate Date Differences Excluding Weekends in MySQL?

Linda Hamilton
Release: 2024-11-01 05:27:27
Original
629 people have browsed it

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);
Copy after login

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;
Copy after login

Result:

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template