mysql-function to count days between 2 dates excluding weekends

Question

I've searched through many examples , good ones I got : Count days between two dates, excluding weekends (MySQL only) How to count date difference excluding weekend and holidays in MySQL Calculate diffference between 2 dates in SQL, excluding weekend days but didn't get most promising solution , so that i can use in my mysql-function for quering lakhs of rows. This one was very new concept , but didn't worked for inputs like `@start_date = '2013-08-03' , @end_date = '2013-08-21'` Expected ans : 13 , its giving only 12, ``` SELECT 5 * (DATEDIFF(@end_date, @start_date) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@start_date) + WEEKDAY(@end_date) + 1, 1); ``` So i'did tried to make it by myself - ``` Concept : Input : 1. period_from_date - from date 2. period_to_date - to date 3. days_to_exclude - mapping : S M T W TH F Sat => 2^0 + 2^6 (sat and sun to exclude) ^ ^ ^ ^ ^ ^ ^ 0 1 2 3 4 5 6 ``` ``` DELIMITER $$ USE `db_name`$$ DROP FUNCTION IF EXISTS `FUNC…

Accepted Answer

UPDATED: If you just need a number of weekdays between two dates you can get it like this

```
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);
```

Note: The function will still work if you switch start `date1` and end `date2` dates.

Sample usage:

```
SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;
```

Output:

```
| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |
```

Here is DBFiddle demo

mysql-function to count days between 2 dates excluding weekends

Solution

Problem

Related problems