How to Calculate Working Days Between Two Dates in MySQL Without a Built-in Function?

How to calculate working days between two dates in MySQL

Many popular spreadsheet programs, such as Excel, provide the NETWORKDAYS() function to calculate the number of working days between two dates. However, MySQL does not have a similar built-in function.

Solution: Mathematical Expression

To calculate the number of working days between two dates in MySQL, you can use the following expression:

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

Explanation:

• DATEDIFF(@E, @S): Calculates the difference in days between the end date (@E) and the start date (@S).
• DIV 7: Divide the difference by 7 to get the number of weeks between dates.
• 5: Multiply the number of weeks by 5 to get the number of working days.
• MID(): Extract the relevant subset of numbers from the string based on the start date and end date. The specific string used in the expression is built from the number of working days between different start and end date combinations.

Assumptions and limitations:

• The end date (@E) cannot be earlier than the start date (@S).
• This function ignores holidays.
• This expression assumes Monday is the first day of the week.

Example:

To calculate the number of working days between '2023-03-06' (@S) and '2023-03-10' (@E), you can use the following query:

<code class="language-sql">SELECT 5 * (DATEDIFF('2023-03-10', '2023-03-06') DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY('2023-03-06') + WEEKDAY('2023-03-10') + 1, 1) AS num_working_days;</code>

This query will return the number of working days between two dates, which is 5.

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