How to get the number of business days using mysql

Question

I have to find the number of business days using mysql.I am using this query but this is not giving me the correct result

 SELECT ((DATEDIFF('2015-05-31', '2015-05-01')) -((WEEK('2015-05-31') - WEEK('2015-05-01')) * 2) - 
(case when weekday('2015-05-31') = 6 then 1 else 0 end) - (case when weekday('2015-05-01') = 5 then 1 else 0 end))
as DifD ;

It is giving 19 as output where number of business days should be 20 Somebody please help

check [MySQL function to find the number of working days between two dates](http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates) — amdixon, Apr 16 '15 at 10:13
works for me for example running query select 5 * (DATEDIFF("2014-12-31", "2014-12-01") DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY("2014-12-01") + WEEKDAY("2014-12-31") + 1, 1); returns 22 — amdixon, Apr 16 '15 at 10:36

score 1 · Answer 1 · answered Apr 16 '15 at 12:03

1

Try this!

SET @i=-1;
SELECT SUM(CASE WHEN(WEEKDAY(ADDDATE('2015-05-01', INTERVAL @i:=@i+1 DAY))) < 5 THEN 1 ELSE 0 END) AS `business_days`
FROM `table`
WHERE @i < DATEDIFF('2015-05-31', '2015-05-01');

Hope this answer helps!

answered Apr 16 '15 at 12:03

George K.

41
2

can it be used without the table – lucifer Apr 16 '15 at 12:12
Maybe you can use this post http://stackoverflow.com/questions/9757919/count-days-between-two-dates-excluding-weekends-mysql-only – George K. Apr 16 '15 at 15:35

score 0 · Answer 2 · answered Jan 10 '22 at 12:57

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

How to get the number of business days using mysql

2 Answers2