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 dat开发者_StackOverflow社区es.
Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays.
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.
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.
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.
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.
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'
improved expression:
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
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? :)
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
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.
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)
Given the first day of a month, this will return the number of weekdays within that month. In MySQL. Without a stored procedure.
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)
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.
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).
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 ;
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
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 ;
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));
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)]
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
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$$
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!
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
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
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...
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
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')
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
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'
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);
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 ;
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
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 ;
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');
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 ;
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
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.
加载中,请稍侯......
精彩评论