MySQL函数，查找两个日期之间的工作天数。

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

Excel有一个NETWORKDAYS()函数，可以在两个日期之间找到业务天数。

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

有人对MySQL有类似的功能吗?由于假期增加了复杂性，解决方案不需要处理假期。

29 个解决方案

#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.

计算开始日期@S和结束日期@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.

假设结束日期(@E)在开始日期之前(@S)。与DATEDIFF兼容，在相同的开始日期和结束日期为零工作日。忽略了假期。

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.

数字串的构造如下。创建一个开始日和结束日的表，行必须从周一(工作日0)开始，并且列必须从星期一开始。从左上到右下的对角线全部填满0(即周一至周一、周二、周二等0个工作日)。每天从对角线开始(必须始终是0)，并在每一天的某一天填入右边的列。如果你在一个周末(非工作日)的专栏里登陆，那么工作日的数量不会改变，它是从左边来的。否则，工作日的数量会增加一个。当您到达行循环的末尾时，返回到同一行的开始，然后继续，直到再次到达对角线。然后继续下一行。

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.

然后将表中的49个值连接到字符串中。

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'

改进的字符串:“0123444401233334012222340111123400001234000123440”

improved expression:

改进的表达式:

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

#2

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.

当然，您需要在应用程序中使用所有日期来填充它。因为一年只有365天(或366天)，从1900年到2100年，这没什么大不了的。只是要确保你把所有的日期都载入了，而不仅仅是假期。

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.

注意:我主要使用MS SQL，而且很长时间没有使用MySQL，所以您可能需要调整上面的内容。例如，我甚至不记得MySQL是否具有位数据类型。

#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.

这个解决方案使用的方法基本上与Rodger的方法相同，但是生成矩阵的方法要复杂得多。注意:此解决方案的输出与网络工作日不兼容。

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.

在Rodger的解决方案中，它计算了开始日期(@S)和结束日期(@E)之间的业务天数，而不必定义存储过程。它假定结束日期不在开始日期之前。使用相同的开始和结束日期将产生0。假期没有考虑在内。

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.

这个和Rodger的解决方案的主要区别在于，矩阵和结果的字符串是由一个复杂的算法构造的，而这个算法是我不包括的。这个算法的输出通过一个单元测试来验证(参见下面的测试输入和输出)。在矩阵中，任意给定的x和y值对的交点(WEEKDAY(@S)和WEEKDAY(@E)在两个值之间的工作日内产生差异。分配顺序实际上是不重要的，因为这两个被加在一起来绘制位置。

Business days are Monday-Friday

工作日是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:

表中的49个值被连接到以下字符串:

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

#4

Could the proposed strings be wrong?

提议的字符串可能出错吗?

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

DATEDIFF(,)排除了“来”。同样地，这个字符串也应该是这样的:

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

周一->周五= {Mon, Tu, Wed, Th} = 4。

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

周一->周六= {Mon, Tu, Wed, Th, Fri} = 5。

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

周二->星期一= {Tu，星期三，星期三，星期五，跳台，跳过太阳，Mon被排除}= 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'

弦:“0123455401234434012332340122123401101234000123450”

Am i missing something here? :)

我是不是漏掉了什么?:)

#5

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

只是为了进一步参考。以上这些都不是我的工作，而是一个修改版的@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)

#6

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

给定一个月的第一天，这将返回当月的工作日天数。在MySQL。没有一个存储过程。

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)

#7

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).

根据Yada上面的函数，这是一个稍微有点变化的主题，它计算从当前日期(不包括)的工作天数，直到目标日期。它还处理了以色列的不同的周末:-)注意，如果目标日期在过去(这正是我想要的)，这将产生一个负面的结果。

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 ;

#8

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

Yada的解决方案不正确。我的变化:

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 ;

#9

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

我不知道在mysql中有什么通用函数。

Sorry!

对不起!

#10

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())

选择ap.WDDIFF(DATE_SUB(CURDATE())， INTERVAL DAYOFMONTH(CURDATE()) - 1天)，CURDATE()

Note: The function counts both start and end dates

注意:函数包括开始日期和结束日期。

#11

Thsi works in Sql Server 2005

Thsi在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

#12

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'

#13

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.

对于上面的NETWORKDAYS()函数，应该在开始日期到结束日期的7天内和整个周末期间添加一个条件。

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

#14

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

虽然很旧，但帮助很大。根据@shahcool提供的解决方案，不返回确切的日期。

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

工作日(“2013-03-26”，“2013-04-01”)返回3天，但实际需要5天。

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 ;

#15

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.

MYSQL函数在两个日期(含)之间返回工作日。2至6日为周一至周五，这可以根据你的日历/地区进行调整。

---

-- 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

#16

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

#17

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.

我需要两个函数。一个计算两个日期之间的工作日数量和一个增加/减去x工作日的日期。以下是我在网上找到的例子。它们的作用是接近标准的DATEDIFF()和DATE_ADD()函数，并互相称赞对方的计算。例如，DateDiffBusiness('2014-05-14'， DateAddBusiness('2014-05-14'，5))将等于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 ;

#18

Helooo test please.

请Helooo测试。

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...

#19

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

模拟网络工作日的函数。INTL基于Rodger Bagnall解决方案https://*.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');

#20

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

这是替代DATEDIFF的一种替代，它适用于+ve和-ve之间的差异。

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 ;

#21

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

#22

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....

我有这样的要求，并写出了完整的函数，可以计算出给定国家的周末和假期(使用单独的表)。我把整个功能和细节都放在了我的博客上(http://mgw.dumatics.com/mysql- functionto - calculatorworktime/)以及解释和流程图，以及节日表的创建等等……我将很乐意把它放在这里,但这有点太长....

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".

让我们假设一个事件在“2016年6月10日星期五中午12点”登陆“英国”的一个网站，该网站在9点到16:00之间开放。这一事件在“2016年6月14日星期二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)]

以上事件功能应计算960分钟= 16小时=[4小时(周五12:00至16:00)+ 7小时(周一09:00至16:00)+ 5小时(09:00 - 14:00)]

#23

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.

如果你想真正地忽略周末的存在，那么你就需要把源自于Sat/Sun的东西视为来自于Mon的东西;有些东西以Sat/Sun结尾，好像它真的在周五结束。因此，在周末开始和结束的事情，你必须忽略开始和结束。我不认为其他的答案会这样。

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):

在Rodger的回答中，创建了上面的字符串的表是下面的(如果它是-1而不是0，在周六/周日结束):

 |  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

#24

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.

@Rodger Bagnall的回答不适合我，例如在2016-04年度。它比真实情况少了1天。

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

查询不优化只是为了显示数字的来源。

#25

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..

如果startdate = enddate, duration = 1，同样…

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

我用大多数投票结果中提到的逻辑计算了这个字符串，并得到了我需要的结果。

#26

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

#27

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.

对于任何开始和结束在同一个周末的日期，比如2018-05-05到周六，2018-05-12，它计算了一天的时间。

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$$

#28

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

您将需要使用DATEDIFF来获得MySQL中两个日期之间的天数。即:

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.

但是Stephane是正确的，假期是联邦和地区定义的。您需要创建一个表来存储日期，并在计算中引用它们。

#29

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

——测试SELECT Networkdays('2009-12-06'， '2009-12-13');