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.
38 Answers
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
- get number of complete weeks and multiply by 5 weekdays
- get the number of days in the piece of leftover week
- if the leftover week goes across a weekend, subtract the 2 weekend days
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$$
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')
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'
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.
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.
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 ;
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
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
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$$
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;
Related