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

Calculating Days between Dates Excluding Weekends (MySQL)

Determining the difference between two dates in MySQL is straightforward using the DATEDIFF() function. However, if you need to exclude weekends from the calculation, a more specialized approach is required.

To achieve this, consider using the following MySQL function:

<code class="mysql">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);</code>

This function takes two dates as inputs and returns the number of days between them, excluding weekends (i.e., Saturdays and Sundays).

Here's a breakdown of its components:

• ABS(DATEDIFF(date2, date1)) 1: Calculates the absolute difference between the two dates, ensuring a positive result.

• `ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),

                ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2`: Calculates the number of weekend days between the two dates.

  Copy after login
• (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1): Checks if the first date is a Monday.
• (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7): Checks if the second date is a Sunday.

By combining these components, the function provides an accurate calculation of the number of days between the two dates, excluding weekends.

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