How to Calculate Business Days Between Two Dates in MySQL (Without Holidays)?

MySQL function calculates the number of working days between two dates (excluding holidays)

Excel’s NETWORKDAYS() function can conveniently calculate the number of working days between two dates, and MySQL also needs similar functionality. Although handling holidays is more complex, a simplified solution is required here that does not take holidays into account.

Solution:

The following expression effectively calculates the number of working days between start date @S and end date @E:

<code class="language-sql">5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)</code>

Assumptions and Notes:

• End date (@E) cannot be before start date (@S).
• Similar to DATEDIFF, if the start date and end date are the same, the number of working days will be zero.
• Holidays are not taken into account.

Construction of numeric string:

The number string is carefully constructed as a table of start and end dates, with Monday (WEEKDAY 0) as the starting point for rows and columns. The diagonal line from the upper left corner to the lower right corner is padded with zeros, indicating that there are no working days between dates within the same week (for example, Monday to Monday).

Moving diagonally to the right, each day has a non-zero working day count only if that day is not a weekend (non-working day). When the end of a line is reached, the count goes back to the beginning of the same line. This process continues until the next diagonal is encountered.

Improved expression:

After modification, the improved expression is:

<code class="language-sql">5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)</code>

This improved expression uses an enhanced numeric string, providing more accurate results.

The above is the detailed content of How to Calculate Business Days Between Two Dates in MySQL (Without Holidays)?. For more information, please follow other related articles on the PHP Chinese website!