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

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 2

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 3

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.

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

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 6

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 ;