Reputation: 31265
MySQL function to find the number of working days between two dates
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.
Upvotes: 55
Views: 104499
Answers (30)
Reputation: 419
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? :)
EDIT:
This appears to be the only answer with the correct string but lacks the expression it's referencing. Adding it here for the sake of making this a complete answer:
SET @START = '2012-08-26';
SET @END = '2012-09-27';
SELECT 5 * (DATEDIFF(@END, @START) DIV 7) + MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@START) + WEEKDAY(@END) + 1, 1);
Upvotes: 26
Reputation: 1
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
Upvotes: 0
Reputation: 11
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)
- calculation of working days in the first week of the month : MID('5432100',weekday(@S)+1 , 1)
- calculation of workig days in the last week of the month : mid('1234555', weekday(@E)+1, 1)
- 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
Upvotes: 1
Reputation: 726
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;
Upvotes: 0
Reputation: 35
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$$
Upvotes: 0
Reputation: 101
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
Upvotes: 0
Reputation: 51
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
Upvotes: 0
Reputation: 123
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 ;
Upvotes: 0
Reputation: 778
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.
Upvotes: 0
Reputation: 19
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.
Upvotes: 0
Reputation: 18238
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
Upvotes: 3
Reputation: 11
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')
Upvotes: 1
Reputation: 68
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$$
Upvotes: 2
Reputation: 1
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
Upvotes: 0
Reputation: 99
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.
Upvotes: 5
Reputation: 2203
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
Upvotes: 1
Reputation: 2235
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
Upvotes: 2
Reputation: 103
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)]
Upvotes: 2
Reputation: 406
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));
Upvotes: 3
Reputation: 22738
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 ;
Upvotes: 0
Reputation: 4784
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');
Upvotes: 0
Reputation: 11
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...
Upvotes: 1
Reputation: 596
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
Upvotes: 20
Reputation: 1
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 ;
Upvotes: 0
Reputation: 199
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
Upvotes: 0
Reputation: 11
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
Upvotes: 1
Reputation: 895
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 ;
Upvotes: 0
Reputation: 1
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);
Upvotes: 0
Reputation: 91
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
Upvotes: 1
Reputation: 2569
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)
Upvotes: 10
Related Questions
- mysql-function to count days between 2 dates excluding weekends
- How to get the number of days of difference between two dates on MySQL?
- SQL User Defined Function to calculate the number of days between two dates
- MySQL Function to add a number of working days to a DATETIME
- mysql query between days and calculate days between dates
- Number of Working Days between two dates[LOGIC]
- MySQL - number of days worked within date range
- How to get the number of days between two dates
- SQL to get number of working days between two dates
- Count Working Days between Dates starting from following day