MySQL function to find the number of working days between two dates

Question

Excel has NETWORKDAYS() function that find the number of business days between two dates.

Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays.

Answer 1

This expression -

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

calculates the number of business days between the start date @S and the end date @E.

Assumes end date (@E) is not before start date (@S). Compatible with DATEDIFF in that the same start date and end date gives zero business days. Ignores holidays.

The string of digits is constructed as follows. Create a table of start days and end days, the rows must start with monday (WEEKDAY 0) and the columns must start with Monday as well. Fill in the diagonal from top left to bottom right with all 0 (i.e. there are 0 working days between Monday and Monday, Tuesday and Tuesday, etc.). For each day start at the diagonal (must always be 0) and fill in the columns to the right, one day at a time. If you land on a weekend day (non business day) column, the number of business days doesn't change, it is carried from the left. Otherwise, the number of business days increases by one. When you reach the end of the row loop back to the start of the same row and continue until you reach the diagonal again. Then go on to the next row.

E.g. Assuming Saturday and Sunday are not business days -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

Please let me know if you find any bugs.

-Edit improved table:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

Answer 2

Could the proposed strings be wrong?

DATEDIFF(from, to) excludes 'to'. In the same way so should this string:

Monday -> friday = {Mon, Tu, Wed, Th} = 4

Monday -> Saturday = {Mon, Tu, Wed, Th, Fri} = 5

Tuesday -> Monday = {Tu, Wed, Th, Fri, skip Sat, skip Sun, Mon is excluded} = 4

and so on

Proposed Matrix:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

String: '0123455401234434012332340122123401101234000123450'

Am i missing something here? :)

Answer 3

This solution uses basically the same approach as Rodger's except the method for generating the matrix is much more complex. Note: This output of this solution is not compatible with NETWORKDAYS.

As in Rodger's solution, this calculates the number of business days between the start date (@S) and the end date (@E) without having to define a stored procedure. It assumes that the end date is not before the start date. Using the same start and end date will produce 0. Holidays are not taken into account.

The major difference between this and Rodger's solution is that the matrix and resulting string of digits is constructed by a complex algorithm which I have not included. The output of this algorithm is validated by a unit test (see the test inputs and outputs below). In the matrix, the intersection of any given x and y value pair (WEEKDAY(@S) and WEEKDAY(@E) yields the difference in work days between the two values. The assignment order is actually unimportant as the two are added together to plot the position.

Business days are Monday-Friday

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

The 49 values in the table are concatenated into the following string:

0123455501234445012333450122234501101234000123450

In the end, the correct expression is:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

I have verified the following inputs and outputs using this solution:

Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5

Answer 4

Since you will need to track holidays somewhere, a Calendar table seems appropriate:

CREATE TABLE Calendar
(
     calendar_date     DATETIME     NOT NULL,
     is_holiday        BIT          NOT NULL,
     is_weekend        BIT          NOT NULL,
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

You of course need to populate it with all dates for whatever time period you might ever work with in your application. Since there are only 365 (or 366) days in a year, going from 1900 to 2100 isn't a big deal. Just make sure that you load it with all dates, not just the holidays.

At that point queries like the one that you need become trivial:

SELECT
     COUNT(*)
FROM
     Calendar
WHERE
     calendar_date BETWEEN '2009-01-01' AND '2009-10-01' AND
     is_holiday = 0 AND
     is_weekend = 0

Caveat: I work mostly with MS SQL and haven't worked with MySQL in a long time, so you may need to tweak the above. For example, I don't even remember if MySQL has the BIT datatype.

Answer 5

Just for futher reference. None of the above worked for me but a modified version of @Jeff Kooser:

SELECT (DATEDIFF(date_end, date_start)) -
        ((WEEK(date_end) - WEEK(date_start)) * 2) -
        (case when weekday(date_end) = 6 then 1 else 0 end) -
        (case when weekday(date_start) = 5 then 1 else 0 end) -
        (SELECT COUNT(*) FROM holidays WHERE holiday>=date_start and holiday<=data_end)

Answer 6

SELECT  5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)

This is when you want to consider the following cases:

1) if startdate = enddate, duration = 1 and likewise..

I calculated the string using the logic mentioned in the most voted answer and got results as I needed.

Answer 7

Given the first day of a month, this will return the number of weekdays within that month. In MySQL. Without a stored procedure.

SELECT (DATEDIFF(LAST_DAY(?),?) + 1) - 
    ((WEEK(LAST_DAY(?)) - WEEK(?)) * 2) -
    (case when weekday(?) = 6 then 1 else 0 end) - 
    (case when weekday(LAST_DAY(?)) = 5 then 1 else 0 end)

Answer 8

Based on the function above by Yada, here's a slight variation on the subject, which calculates work days left from the current date (not including), till the target date. It also handles the different weekend days in Israel :-) Note that this will produce a negative result if the target date is in the past (which is just what I wanted).

DELIMITER //
DROP FUNCTION IF EXISTS WORKDAYS_LEFT//

CREATE FUNCTION WORKDAYS_LEFT(target_date DATE, location char(2))
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE check_date DATE;
  DECLARE diff INT;
  DECLARE extra_weekend_days INT;
  DECLARE weeks_diff INT;

  SET start_date = CURDATE();
  SET end_date = target_date;
  SET diff = DATEDIFF(end_date, start_date);
  SET weeks_diff = FLOOR(diff / 7);
  SET end_date = DATE_SUB(end_date, INTERVAL (weeks_diff * 7) DAY);
  SET check_date = DATE_ADD(start_date, INTERVAL 1 DAY);
  SET extra_weekend_days = 0;
  WHILE check_date <= end_date DO
    SET extra_weekend_days = extra_weekend_days +
      IF(DAYNAME(check_date) = 'Saturday', 1, 0) +
      IF(DAYNAME(check_date) = IF(location = 'IL','Friday', 'Sunday'), 1, 0);
    SET check_date = DATE_ADD(check_date, INTERVAL 1 DAY);
  END WHILE;

  RETURN diff - weeks_diff*2 - extra_weekend_days;
END//

DELIMITER ;

Answer 9

OK Boys and Girls, I've got obviously the best solution, here is a simple select statement to get number of weekdays between 2 dates.

select 
    FLOOR(DATEDIFF(later_date, earlier_date) / 7) * 5 +  
    least(DATEDIFF(later_date, earlier_date) % 7, 5) + 
    if(weekday(later_date) < weekday(earlier_date), -2, 0);

A SIMPLE EXPLANATION

1. get number of complete weeks and multiply by 5 weekdays
2. get the number of days in the piece of leftover week
3. if the leftover week goes across a weekend, subtract the 2 weekend days

Answer 10

Yada's solution doesn't work correctly. My changes:

DELIMITER $$

DROP FUNCTION IF EXISTS `catalog`.`WORKDAYS` $$
CREATE FUNCTION `catalog`.`WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE diff INT;

  IF (first_date < second_date) THEN
    SET start_date = first_date;
    SET end_date = second_date;
  ELSE
    SET start_date = second_date;
    SET end_date = first_date;
  END IF;

  SET diff = DATEDIFF(end_date, start_date);

  RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN diff
               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1)
               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1)

               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
                    && WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2)
               ELSE diff END)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);

END $$

DELIMITER ;

Answer 11

This query easily returns the number of working days between two dates exclude weekends:

select datediff('2016-06-19','2016-06-01') - (floor(datediff('2016-06-19','2016-06-01')/6) + floor(datediff('2016-06-19','2016-06-01')/7));

Answer 12

I had this requirement and have written complete function that can calculate while avoiding hours of weekend and holidays for a given country (using a separate table). I have put the whole function and details on my blog (http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/) along with explanation and flowchart and creation of holiday table etc...I would gladly put it here but it's a bit too long....

Example of problem resolved:

Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00".

For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)]

Answer 13

If you want to truly ignore the existence of weekends, then you need to treat something that originates on Sat/Sun as if it originated on Mon; and something that ends on Sat/Sun as if it really ended on Fri. Therefore, something that starts and ends on a weekend, you have to ignore both the start and end. I don't think any of the other answers do this.

The following function does this:

CREATE DEFINER=`root`@`localhost` FUNCTION `weekdayDiff`
(
edate datetime,
sdate datetime
)
RETURNS int
DETERMINISTIC

BEGIN
if edate>sdate
then
 return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(sdate) + WEEKDAY(edate)) + 1, 2);
else
 return -(5 * (DATEDIFF(sdate, edate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(edate) + WEEKDAY(sdate)) + 1, 2));
end if;

-- The following works unless both start and finish date are on weekends.
-- return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sdate) + WEEKDAY(edate) + 1, 1);

END;

In the language of Rodger's answer, the table that created the string above is below (the only difference if it's -1 instead of 0 for starting and ending on a saturday/sunday):

 |  M  T  W  T  F  S  S
-|---------------------
M| +0 +1 +2 +3 +4 +4 +4
T| +4 +0 +1 +2 +3 +3 +3
W| +3 +4 +0 +1 +2 +2 +2
T| +2 +3 +4 +0 +1 +1 +1
F| +1 +2 +3 +4 +0 +0 +0
S| +0 +1 +2 +3 +4 -1 -1
S| +0 +1 +2 +3 +4 +4 -1

Answer 14

The top answer counted for the days between the start date and end date but excluded the end date.

Also for any dates that began and end on the same weekend days, say Saturday 2018-05-05 to Saturday 2018-05-12, it calculated one day more.

Here is a function that works perfectly for me!

drop procedure if exists get_duration$$
create procedure get_duration(in data_from date, in data_to date)
begin
    if (WEEKDAY(data_from) = 5 AND WEEKDAY(data_to) = 5) 
    OR (WEEKDAY(data_from) = 6 AND WEEKDAY(data_to) = 6) then
        select (5 * (DATEDIFF(data_to, data_from) DIV 7) 
        + MID('0123444401233334012222340111123400001234000123440',
        7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1)) dur;
    else 
        select (5 * (DATEDIFF(data_to, data_from) DIV 7) 
        + MID('0123444401233334012222340111123400001234000123440',
        7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1))+1 dur;
    end if;
end$$

Answer 15

Non-weekend days difference can be achieved this way:

CREATE FUNCTION `WDDIFF` (d0 DATE, d1 DATE) 
  RETURNS INT DETERMINISTIC 
  COMMENT 'Date0, Date1' 
BEGIN 
  RETURN DATEDIFF(d1, d0) - (DATEDIFF(DATE_SUB(d1, INTERVAL WEEKDAY(d1) DAY), DATE_ADD(d0, INTERVAL (7 - WEEKDAY(d0)) DAY))/7+1)*2 + IF(WEEKDAY(d0)>4, 1, 0) + 1; 
END

Usage: Week days since begin of month

SELECT ap.WDDIFF(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY), CURDATE())

Note: The function counts both start and end dates

Answer 16

The problem you'll have with the "ignoring holiday" par is each country will have different holiday.

You'll have to begin by defining the holidays for your country and then pass through them to see if a certain date is a holiday.

I don't know of a generic functions that do what you want in mysql

Sorry!

Answer 17

MYSQL Function returning business days between 2 dates (inclusive). The BETWEEN 2 AND 6 is Monday-Friday, this can be adjusted based on your calendar /region.

---

-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetBusinessDaysBetweenDates`(d1 DATE, d2 DATE) RETURNS int(11)
BEGIN
    DECLARE bDaysInPeriod INT;

    SET bDaysInPeriod=0;
    WHILE d1<=d2 DO
        IF DAYOFWEEK(d1) BETWEEN 2 AND 6 THEN
            SET bDaysInPeriod=bDaysInPeriod+1;
        END IF;

        SET d1=d1+INTERVAL 1 day;
    END WHILE;

    RETURN bDaysInPeriod;
END

Answer 18

Helooo test please.

DELIMITER $$

DROP FUNCTION IF EXISTS `WORKDAYS` $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE diff INT;
  DECLARE cnt INT;

  IF (first_date < second_date) THEN
    SET start_date = first_date;
    SET end_date = second_date;
  ELSE
    SET start_date = second_date;
    SET end_date = first_date;
  END IF;

   SELECT COUNT(*) INTO cnt FROM `holiday` WHERE (hday BETWEEN start_date AND end_date) and (DAYOFWEEK(hday) != 7 and DAYOFWEEK(hday) != 1);

  SET diff = DATEDIFF(end_date, start_date) ;

  RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN (diff - cnt)
               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2 - cnt)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1 - cnt)
               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1 - cnt)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1 - cnt)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)

               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
                    && WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2 - cnt)
               ELSE (diff - cnt)  END)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);

END $$

and table holiday

DROP TABLE IF EXISTS `holiday`;
CREATE TABLE `holiday` (
  `id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
  `hday` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `holiday` (`id`, `hday`) VALUES
(1, '2012-01-01'),
(2, '2012-05-01'),
(3, '2012-05-08'),
(4, '2012-07-05'),
(5, '2012-07-06'),
(6, '2012-09-28'),
(7, '2012-10-28'),
(8, '2012-11-17'),
(9, '2012-12-24'),
(10,    '2012-12-25'),
(11,    '2012-12-26');
etc...

Answer 19

Answer posted by @Rodger Bagnall not works correctly for me, for example on 2016-04. It shows 1 day less that it is in real.

if talking about calculating by query - i use this :

set
@S = '2016-04-01',
@E = '2016-04-30';
 select
    case 
        when WEEKDAY(@S) < 5 then 5 - WEEKDAY(@S)
        else 0
    end #startweek
    +
    case 
        when WEEKDAY(@E) < 5 then WEEKDAY(@E) + 1
        else 5
    end #endweek
    +
    (
        DATEDIFF(@E, @S) + 1 # plus 1 day cause params is inside 1 month
        - (7 - WEEKDAY(@S)) # minus start week
        - (WEEKDAY(@E) + 1) # minus end week
    ) DIV 7 * 5 #rest part


    as work_date_count;

Query not optimized just to show where numbers come from

Answer 20

I added a stored procedure in my MySQL DB to count the total working days of my team (I called it WORKDAYS):

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)
     - (SELECT DISTINCT COUNT(PriKey) FROM holidays WHERE date BETWEEN date1 AND date2)
     + (SELECT DISTINCT COUNT(PriKey) FROM weekenddaysworked WHERE date BETWEEN date1 AND date2)

I added two tables to my DB: holidays and weekenddaysworked both with two columns (PriKey (int, 11), data (date))

In holidays I added the holidays I needed to be taken into account and in weekenddaysworked I added dates where my guys worked on the weekend.

I added the procedure as a function with an INT as result. date1 and date2 are defined as DATE.

Now I can call the MySQL function like so:

WORKDAYS(date1,date2) - so for example WORKDAYS('2018-11-01','2018-12-01')

Answer 21

the solutions based on mid('0123...') started by Rodger Bagnall has a flaw as on the last week of the month, the remaining working day can end in the middle of the last week of the month. The calculation (for example) "monday to friday = 5 working day" is false as if the end of the month is a thursday for example, the calculation should be "monday to [not friday but] the last day of the month that is thursday in this example, that is 4 working days".

A possible solution, which doesn't require stores procedures is this one :

select MID('5432100',weekday(@S)+1 , 1)+
mid('1234555', weekday(@E)+1, 1)+
floor((DATEDIFF(@E,@S)+1
-MID('5432100',weekday(@S)+1 , 1)
-mid('1234555', weekday(@E)+1, 1))/7)*5

The strategy is : a month is divided in these weeks :

• first week : which can be not entire : can be 0
• last week : which can be not entire : can be 0
• others weeks, which are entire (7 days)

1. calculation of working days in the first week of the month : MID('5432100',weekday(@S)+1 , 1)
2. calculation of workig days in the last week of the month : mid('1234555', weekday(@E)+1, 1)
3. calculation of all the "entire weeks" : that is all the days in the month (DATEDIFF(@E,@S)+1), minus the numbers of days already calculated previously, divide by 7 for the number of weeks, take the floor part, then multiply by 5 for the number of days

Answer 22

For the NETWORKDAYS() function above, one more condition should be added to cover cases when the start date to end date is within 7 days and across a weekend.

    RETURN (diff + 1)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END)
    - (CASE WHEN diff<7 and WEEK(start_date)<>WEEK(end_date) THEN 2 ELSE 0 end);

Answer 23

Although very an OLD Post but helping a lot. As Per the Solution Provided By @shahcool is not Returning Exact Days e.g.

Workdays('2013-03-26','2013-04-01') Return 3 Days But actually There Must be 5 Days

Below is the Solution which i have tested and Retrun exact Working Days

DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYS $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE NumberOfWeeks INT;
DECLARE RemainingDays INT;
DECLARE firstDayOfTheWeek INT;
DECLARE lastDayOfTheWeek INT;
DECLARE WorkingDays INT;  

IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;

## Add one to include both days in interval
SET diff = DATEDIFF(end_date, start_date)+1;
SET NumberOfWeeks=floor(diff/7);
SET RemainingDays=MOD(diff,7);
SET firstDayOfTheWeek=DAYOFWEEK(start_date);
SET lastDayOfTheWeek=DAYOFWEEK(end_date); 


IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN 

   IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET        RemainingDays=RemainingDays-1; END IF;
   IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF; 
   ELSE
       IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
         IF (lastDayOfTheWeek=6) THEN  SET RemainingDays=RemainingDays-1; END IF;  
       ELSE SET RemainingDays=RemainingDays-2;
       END IF;
   END IF;

   SET WorkingDays=NumberOfWeeks*5;

   IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays;

   ELSE RETURN WorkingDays; END IF;

 END $$

 DELIMITER ;

Answer 24

Thsi works in Sql Server 2005

Dont know if it is gonna work for you.

DECLARE @StartDate DATETIME,
        @EndDate DATETIME

SELECT  @StartDate = '22 Nov 2009',
        @EndDate = '28 Nov 2009'

;WITH CTE AS(
        SELECT  @StartDate DateVal,
                DATENAME(dw, @StartDate) DayNameVal
        UNION ALL
        SELECT  DateVal + 1,
                DATENAME(dw, DateVal + 1)
        FROM    CTE
        WHERE   DateVal < @EndDate
)
SELECT  COUNT(1)
FROM    (
            SELECT *
            FROM CTE
            WHERE DayNameVal NOT IN ('Sunday','Saturday')
        ) DayVals

Answer 25

Below function will give you the Weekdays, Weekends, Date difference with proper results:

You can call the below function like,
select getWorkingday('2014-04-01','2014-05-05','day_diffs');
select getWorkingday('2014-04-01','2014-05-05','work_days');
select getWorkingday('2014-04-01','2014-05-05','weekend_days');




    DROP FUNCTION IF EXISTS PREPROCESSOR.getWorkingday;
    CREATE FUNCTION PREPROCESSOR.`getWorkingday`(d1 datetime,d2 datetime, retType varchar(20)) RETURNS varchar(255) CHARSET utf8
    BEGIN
     DECLARE dow1, dow2,daydiff,workdays, weekenddays, retdays,hourdiff INT;
        declare newstrt_dt datetime;
       SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays into daydiff, workdays, weekenddays
      FROM (
       SELECT
         dd.iDiff,
         ((dd.iWeeks * 2) + 
          IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) + 
          IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
           FROM (
          SELECT  dd.iDiff, FLOOR(dd.iDiff / 7) AS iWeeks, dd.iDiff % 7 iDays, 5 - dd.iStartDay AS iSatDiff,  6 - dd.iStartDay AS iSunDiff
         FROM (
          SELECT
            1 + DATEDIFF(d2, d1) AS iDiff,
            WEEKDAY(d1) AS iStartDay
          ) AS dd
        ) AS dd
      ) AS dd ;
      if(retType = 'day_diffs') then
      set retdays = daydiff; 
     elseif(retType = 'work_days') then
      set retdays = workdays; 
     elseif(retType = 'weekend_days') then  
      set retdays = weekenddays; 
     end if; 
        RETURN retdays; 
        END;


Thank You.
Vinod Cyriac.
Bangalore

Answer 26

I needed two functions. One to calculate the number of business days between two dates and one to add/subtract x business days to a date. Here is what I put together from examples I found on the internet. They are made to be close to the standard DATEDIFF() and DATE_ADD() functions as well as compliment each others' calculations. For example, DateDiffBusiness('2014-05-14', DateAddBusiness('2014-05-14',5)) will equal 5.

DROP FUNCTION IF EXISTS DateDiffBusiness;
DELIMITER &
CREATE FUNCTION DateDiffBusiness( d2 DATE, d1 DATE )
RETURNS INT
DETERMINISTIC
COMMENT 'Calculates the number of bussiness days between two dates'
BEGIN
  DECLARE dow1, dow2, days INT;
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(d2);
  SET days = FLOOR( DATEDIFF(d2,d1)/7 ) * 5 +
             CASE
               WHEN dow1=1 AND dow2=7 THEN 5
               WHEN dow1 IN(7,1) AND dow2 IN (7,1) THEN 0
               WHEN dow1=dow2 THEN 1
               WHEN dow1 IN(7,1) AND dow2 NOT IN (7,1) THEN dow2-1
               WHEN dow1 NOT IN(7,1) AND dow2 IN(7,1) THEN 7-dow1
               WHEN dow1<=dow2 THEN dow2-dow1+1
               WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
               ELSE 0
             END;
  RETURN days-1;
END&
DELIMITER ;


DROP FUNCTION IF EXISTS DateAddBusiness;
DELIMITER &
CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT) 
RETURNS DATE
DETERMINISTIC
COMMENT 'Adds bussiness days between two dates'
BEGIN
 DECLARE num_week INT DEFAULT 0;
 DECLARE num_day INT DEFAULT 0;
 DECLARE adj INT DEFAULT 0;
 DECLARE total INT DEFAULT 0;
 SET num_week = numday DIV 5;
 SET num_day = MOD(numday, 5);
 IF (WEEKDAY(mydate) + num_day >= 5) then
  SET adj = 2;
 END IF;
 SET total = num_week * 7 + adj + num_day;
 RETURN DATE_ADD(mydate, INTERVAL total DAY);
END&
DELIMITER ;

Answer 27

A function that emulates the NETWORKDAYS.INTL based on Rodger Bagnall solution https://stackoverflow.com/a/6762805/218418

DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//
CREATE FUNCTION NETWORKDAYS(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  RETURN (5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1))+1;
END//
DELIMITER ;

And to select

SELECT NETWORKDAYS('2015-01-01 06:00:00', '2015-01-20 06:00:00');

Answer 28

This is a drop in replacement for DATEDIFF that works for both +ve and -ve differences.

DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYSDIFF$$
CREATE FUNCTION WORKDAYSDIFF(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
 RETURN IF (sd >= ed, 
    5 * (DATEDIFF(sd, ed) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(ed) + WEEKDAY(sd) + 1, 1),
  -(5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1)) );
END$$
DELIMITER ;

Answer 29

I use this solution, finally, please see:

DROP FUNCTION IF EXISTS datediff_workdays;
CREATE FUNCTION datediff_workdays(start_date DATE, end_date DATE) RETURNS INTEGER
BEGIN
  RETURN 5 * (DATEDIFF(end_date, start_date) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(start_date) + WEEKDAY(end_date) + 1, 1);
END

Answer 30

I know this is an old thread, but was thinking that my solution might be helpful for some people. this is a query that I did to find the biz days without the need of functions. you can name the fields what you want, I just left them blank on purpose.

SELECT

   @tmp_s   := ept.`date_start`,
   @tmp_e   := IF(ept.`date_end` IS NULL, NOW(),ept.`date_end`),
   @start   := IF(DAYOFWEEK(@tmp_s)=1,@tmp_s + INTERVAL 1 DAY,(IF(DAYOFWEEK(@tmp_s)=7,@tmp_s + INTERVAL 2 DAY,@tmp_s)),
   @end     := IF(DAYOFWEEK(@tmp_e)=1,@tmp_e - INTERVAL 2 DAY,(IF(DAYOFWEEK(@tmp_e)=7,@tmp_e - INTERVAL 1 DAY,@tmp_e)),
   @bizdays := CASE
                  WHEN DATEDIFF(@end,@start)>7 THEN CEIL((DATEDIFF(@end,@start)/7)*5)
                  WHEN DAYOFWEEK(@end)< DAYOFWEEK(@start) THEN DATEDIFF(@end,@start)-2
                  ELSE DATEDIFF(@end,@start)
               END,
   DATE(@start),
   DATE(@end),
   IF(@bizdays>=10,10,@bizdays)

FROM `employee_points` ept
WHERE ept.`date_start` > '2011-01-01'

Answer 31

Building a little on @caveman, @bryan-geraghty and @rodger-bagnall's answers, I needed a version that could also calculate backwards for "weekdays ago" queries. This adaptation works when start_date is before or after end_date.

SELECT 5 * (DATEDIFF(@E, @S) DIV 7) +
  CASE WHEN @E < @S THEN
    -1 * MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@E) + WEEKDAY(@S) + 1, 1)
  ELSE
    MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
  END

Sample results from both cases:

+------------+------------+-----------+
| @S         | @E         | wday_diff |
+------------+------------+-----------+
| 2019-11-25 | 2019-10-26 |       -20 |
| 2019-11-25 | 2019-11-28 |         3 |
+------------+------------+-----------+

Please let me know if you find any bugs.

Answer 32

SELECT FLOOR((DATEDIFF(@E,@S)+1)/7)*5+
    LEAST((DATEDIFF(@E,@S)+1)%7,5)+
    IF(WEEKDAY(@E)<WEEKDAY(@S),IF(WEEKDAY(@S)<5,-2,WEEKDAY(@S)-7),
        IF(WEEKDAY(@E)=WEEKDAY(@S),IF(WEEKDAY(@E) IN (5,6),-1,0),
            IF(WEEKDAY(@S)=5,-1,IF(WEEKDAY(@E)=5,-1,IF(WEEKDAY(@E)=6,-2,0)))));

this is the correction on the functional answer of @jeffery_the_wind from 2019 August below.

1) weeks*5

2) rest days in month added

3) checking days and calculating correction for the rest days.

Answer 33

Here is my solution

DELIMITER $$

DROP FUNCTION IF EXISTS WORKINGDAYS$$
CREATE DEFINER = 'root'@'localhost'
FUNCTION WORKINGDAYS(DATEFROM DATETIME,
                     DATETO   DATETIME
                     )
RETURNS INT(11)
BEGIN
  DECLARE ACTUALDATE  DATETIME;
  DECLARE WORKINGDAYS INTEGER;

  SET WORKINGDAYS = 0;
  SET ACTUALDATE = DATEFROM;

dateloop:
  LOOP

    IF (ACTUALDATE > DATETO OR DATEFROM > DATETO) THEN
      LEAVE dateloop;
    END IF;

    IF (dayofweek(ACTUALDATE) != 7 AND dayofweek(ACTUALDATE) != 1) THEN
      SET WORKINGDAYS = WORKINGDAYS + 1;
    END IF;

    SET ACTUALDATE = adddate(ACTUALDATE, INTERVAL 1 DAY);

  END LOOP dateloop;

  RETURN WORKINGDAYS;
END
$$

DELIMITER ;

Answer 34

Calculates the number of business days between the start date @S and the end date @E without the need for the string of digits:

@SD = DAYOFWEEK(@S)
@ED = DAYOFWEEK(@E)
@DD = DATEDIFF(@S,@E)

IF(@ED<@SD,@DD -(2*FLOOR(@DD/7))-2,@DD -(2*FLOOR(@DD/7)))

Matches against Excel's NETWORKDAYS

Answer 35

select datediff(max_date, min_date) - (2 * (week(max_date)-week(min_date)));

2021-09-06 | 2021-09-10 = 4

2021-09-06 | 2021-09-07 = 1

2021-09-06 | 2021-09-06 = 0

2021-09-06 | 2021-09-09 = 3

2021-09-10 | 2021-09-13 = 1

2021-09-06 | 2021-09-13 = 5

2021-09-06 | 2021-09-17 = 9

Answer 36

drop procedure COUNTWEEKDAYS;
DELIMITER $$

    CREATE PROCEDURE COUNTWEEKDAYS (FROMDATE TIMESTAMP, TODATE TIMESTAMP)
    begin
    declare NOOFWEEKDAYS INTEGER;
    set NoOfWeekDays = (datediff(todate, fromdate) + 1)
                -((timestampdiff(week, FROMDATE , TODATE) * 2))
                -weekday(fromdate)%4
                -weekday(todate)%4;
    select NOOFWEEKDAYS;
    end$$

Answer 37

In MySQL 8.x (long version - self explained)

WITH
    setup AS (
        SELECT ('2022-09-01') first_day, LAST_DAY('2022-09-01') last_day
    ),
    cte AS (
        SELECT s.first_day, s.last_day,
               WEEK(s.first_day) first_week, WEEK(s.last_day) last_week
            FROM setup s
    )
SELECT first_day, last_day, DATEDIFF(last_day, first_day) days_between,
       (DATEDIFF(last_day, first_day) - 2 * (last_week - first_week) + 1) workdays
    FROM cte;

in short:

WITH
    cte AS (
        SELECT
            ('2022-09-01')first_day, ('2022-09-30')last_day,
            WEEK('2022-09-01') first_week, WEEK('2022-09-30') last_week
    )
SELECT (DATEDIFF(last_day, first_day) - 2 * (last_week - first_week) + 1) workdays_without_hollidays
    FROM cte;

Answer 38

Use a cte to create a list of dates in the month, and the weekday value. You can then use that CTE to join to your holidays table and filter however you like. This example gets the first and last day of the current month, but you can change that out for your dates.

with my_days as #gets all dates between the two dates and the weekday value
(
    SELECT ADDDATE(date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH), INTERVAL @i:=@i+1 DAY) AS mydate,
        weekday(ADDDATE(date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH), INTERVAL @i DAY)) as mywkday
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF(last_day(curdate()),date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH))
)
select count(*)
from my_days
where
    mywkday not in (5,6) #exclude saturday and sunday
    and mydate<=curdate() # filter on your date

Answer 39

DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//

CREATE FUNCTION NETWORKDAYS(first_date DATE, second_date DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE diff INT;

  IF (first_date < second_date) THEN
    SET start_date = first_date;
    SET end_date = second_date;
  ELSE
    SET start_date = second_date;
    SET end_date = first_date;
  END IF;

  SET diff = DATEDIFF(end_date, start_date);

  RETURN (diff + 1)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);
END//

DELIMITER ;

-- test SELECT Networkdays('2009-12-06', '2009-12-13');

Answer 40

You'll need to use DATEDIFF in order to get the number of days between two dates in MySQL. IE:

DATEDIFF(t.date_column_1, t.date_column_2)

But Stephane is otherwise correct - holidays are federal and regionally defined. You need to create a table to store the dates & reference them in your calculation.