How to Calculate Business Days Between Two Dates in MySQL?

Calculate working days between two dates in MySQL

Excel’s NETWORKDAYS() function provides a convenient way to determine how many working days there are between two specified dates. However, MySQL lacks similar built-in functions.

To solve this problem, here is a MySQL expression that can be used as an alternative:

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

This expression contains the following calculations:

1. Calculate working day difference: (DATEDIFF(@E, @S) DIV 7) Calculate how many weeks there are between start date @S and end date @E. Divide by 7 to get the number of complete weeks, we multiply it by 5 to account for the number of working days per week.
2. Calculate the start and end working day index: 7 WEEKDAY(@S) and 7 WEEKDAY(@E) Calculate the working day index of the start date and end date (Monday is 0, 1 for Tuesday, and so on). The addition of 1 is to match the index with the numeric string discussed below.
3. Retrieve weekday count: MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) WEEKDAY(@E) 1, 1) Retrieves a single character from the provided numeric string. This character represents the number of working days between the specified combination of working days.

The provided numeric string is constructed to represent the number of working days between each working day combination (Monday to Monday, Tuesday to Tuesday, etc.). By combining the weekday indexes and retrieving the corresponding characters, we get the weekday count.

Assumptions and limitations:

• This function assumes that @E is no earlier than @S.
• It ignores public holidays, so the calculated working day count may not be accurate for scenarios involving holidays.
• The number string is hardcoded into the expression and may need to be updated if working day conventions differ (e.g. if Sunday is considered a working day).

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