How to count date difference excluding weekend and holidays in MySQL

Question

I need to count days (business days) between two dates excluding weekend (most important) and holidays

SELECT DATEDIFF(end_date, start_date) from accounts

But, I don't know how am I supposed to do it in MySQL, I found this article Count days between two dates, excluding weekends (MySQL only). I cannot figure out how to functional query in mysql, Can you give some information of how can achieve this with mysql query. If I am missing anything let me know.

[EDIT]

CREATE TABLE `candidatecase` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
  `CreatedBy` int(11) NOT NULL,
  `UseraccountID` int(11) NOT NULL COMMENT 'User Account ID',
  `ReportReadyID` int(11) DEFAULT NULL COMMENT 'Report Ready ID',
  `DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date Created',
  `InitiatedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Initiated',
  `ActualCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Completed Case',
  `ProjectedCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Projected Finish',
  `CheckpackagesID` int(11) DEFAULT NULL COMMENT 'Default Check Package Auto Assign Once Initiate Start',
  `Alacartepackage1` int(11) DEFAULT NULL COMMENT 'Ala carte Request #2',
  `Alacartepackage2` int(11) DEFAULT NULL COMMENT 'Ala carte Request #3',
  `OperatorID` int(11) NOT NULL COMMENT 'User Account - Operator',
  `Status` int(11) NOT NULL COMMENT 'Status',
  `caseRef` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=293 ;

--
-- Dumping data for table `candidatecase`
--

INSERT INTO `candidatecase` (`ID`, `CreatedBy`, `UseraccountID`, `ReportReadyID`, `DateCreated`, `InitiatedDate`, `ActualCompletedDate`, `ProjectedCompletedDate`, `CheckpackagesID`, `Alacartepackage1`, `Alacartepackage2`, `OperatorID`, `Status`, `caseRef`) VALUES
(1, 43, 70, NULL, '2011-07-22 02:29:31', '2011-07-07 07:27:44', '2011-07-22 02:29:31', '2011-07-17 06:53:52', 11, NULL, NULL, 44, 6, ''),
(2, 43, 74, NULL, '2012-04-03 04:17:15', '2011-07-11 07:07:23', '2011-07-13 05:32:58', '2011-07-21 07:01:34', 20, 0, 0, 51, 0, ''),
(3, 43, 75, NULL, '2011-07-29 04:10:07', '2011-07-11 07:27:12', '2011-07-29 04:10:07', '2011-07-21 07:02:14', 20, NULL, NULL, 45, 6, ''),
(4, 43, 78, NULL, '2011-07-18 03:32:27', '2011-07-11 07:51:31', '2011-07-13 02:18:34', '2011-07-21 07:37:53', 20, NULL, NULL, 45, 6, ''),
(5, 43, 76, NULL, '2011-07-29 04:09:19', '2011-07-11 07:51:11', '2011-07-29 04:09:19', '2011-07-21 07:38:30', 20, NULL, NULL, 45, 6, ''),
(6, 43, 77, NULL, '2011-07-18 03:32:49', '2011-07-11 07:51:34', '2011-07-18 02:18:46', '2011-07-21 07:39:00', 20, NULL, NULL, 45, 6, ''),
(7, 43, 79, NULL, '2011-07-18 03:33:02', '2011-07-11 07:53:24', '2011-07-18 01:50:12', '2011-07-21 07:42:57', 20, NULL, NULL, 45, 6, ''),
(8, 43, 80, NULL, '2011-07-29 04:10:38', '2011-07-11 07:53:58', '2011-07-29 04:10:38', '2011-07-21 07:43:14', 20, NULL, NULL, 45, 6, ''),
(9, 43, 81, NULL, '2011-07-18 03:31:54', '2011-07-11 07:53:49', '2011-07-13 02:17:02', '2011-07-21 07:43:43', 20, NULL, NULL, 45, 6, ''),
(11, 43, 88, NULL, '2011-07-18 03:15:53', '2011-07-13 04:57:38', '2011-07-15 08:57:15', '2011-07-23 04:39:14', 12, NULL, NULL, 44, 6, ''),
(13, 43, 90, NULL, '2011-07-26 07:39:24', '2011-07-13 12:16:48', '2011-07-26 07:39:24', '2011-07-23 12:13:50', 15, NULL, NULL, 51, 6, ''),
(63, 43, 176, NULL, '2011-09-13 08:23:13', '2011-08-26 10:00:32', '2011-09-13 08:23:13', '2011-09-05 09:58:47', 41, NULL, NULL, 45, 6, ''),
(62, 43, 174, NULL, '2011-08-24 03:54:30', '2011-08-24 03:53:13', '2011-08-24 03:54:30', '2011-08-29 03:52:48', 17, NULL, NULL, 51, 6, ''),
(61, 43, 173, NULL, '2011-08-24 03:55:05', '2011-08-24 03:53:39', '2011-08-24 03:55:05', '2011-08-29 03:52:36', 17, NULL, NULL, 51, 6, ''),
(60, 43, 172, NULL, '2011-08-24 03:22:41', '2011-08-24 03:21:50', '2011-08-24 03:22:41', '2011-08-29 03:21:11', 17, NULL, NULL, 51, 6, ''),
(59, 43, 171, NULL, '2011-08-24 03:23:19', '2011-08-24 03:22:00', '2011-08-24 03:23:19', '2011-08-29 03:20:57', 17, NULL, NULL, 51, 6, '');

Does it have to be done all in mysql? You may need a seperate table with the holiday dates on it? And some kind of where clause in your query. — Robbo_UK, Apr 26 '12 at 09:56
Yes, I do have holiday table and yes it has to be done in mysql. — Akram, Apr 26 '12 at 09:59

score 26 · Accepted Answer · edited May 23 '17 at 12:17

26

You might want to try this:

Count the number of working days (took it from here)

SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)

This gives you 261 working days for 2012.
Now you need to know your holidays that are not on a weekend

SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6

The result of this depends on your holiday table.
We need to get that in one query:

SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)

This should be it.

Edit: Please be aware that this only works properly if your end date is higher than your start date.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 26 '12 at 10:09

Thorsten

5,634
6
35
33

This is working ok, but I just got negative output like -14 instead of 14. – Akram Apr 26 '12 at 10:20
You have to limit the subquery of your holidays to the date range you are looking for in your main query as well. (`SELECT COUNT(*) FROM holidays ... WHERE date BETWEEN X AND Y`). And you should not have duplicate items in your holidays table (or change the subquery to not recognize them). – Thorsten Apr 26 '12 at 10:30
I field to succeed, let me post the script – Akram Apr 26 '12 at 10:55
Can you adjust your script to above query. here is my query – Akram Apr 26 '12 at 11:15
`SELECT 5 * (DATEDIFF(ActualCompletedDate,DateCreated ) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(DateCreated) + WEEKDAY(ActualCompletedDate) + 1, 1) - (SELECT COUNT(*) FROM my_holidays WHERE DAYOFWEEK(holiday_date) < 6) from candidatecase` – Akram Apr 26 '12 at 11:16
Sorry, I don't really know what you need ;-) If it is not working right, please post your holidays table and data as well, so I don't have to make up one. – Thorsten Apr 26 '12 at 14:31
1

I can see one of your problems is that in your data you have `created` dates that are higher that the `completed` dates, that turns your result to be negative. I honestly don't know if the query in my answer returns you correct values for this, you need to check that. If you then want positive results you might want to use MySQLs `abs()` function. – Thorsten Apr 26 '12 at 14:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10553/discussion-between-akram-and-thorsten) – Akram Apr 26 '12 at 14:38
2

`DAYOFWEEK()` returns 1 for Sunday and 7 from Saturday so I believe the condition for holidays should be `DAYOFWEEK(holiday_date) between 2 and 6`. – Izydorr Dec 22 '15 at 14:52
Not correct for same dates in 2013 and 2018. It returns 22 but should be 23 for Sat and Sun as off days – Eslam Sameh Ahmed Mar 07 '18 at 13:12

score 4 · Answer 2 · answered Apr 26 '12 at 10:06

Create a table that contains all the weekends and holidays for the next 100whatever years.

You need to be able to specify when a day is a 'holiday' given that no one knows what the holidays will be for 2052 yet, you will not be able to make an accurate function at this time anyway. just update your non-work day table each year when the holidays become known (but you will always know the weekends).

Then your query becomes:

SELECT DATEFIFF(end_date, start_date) - COALESCE((SELECT COUNT(1) FROM nonWorkDays WHERE nonWorkDays.date BETWEEN start_date AND end_date), 0)
FROM accounts

If you really need to write a DATEDIFFWITHOUTWEEKENDSORHOLIDAYS function then just use the above and create a function (there's plenty of resources on how to make functions in each RDBMS).. just be sure to give it a better name. ^_^

One thing you will need to fix is I think there's a +1 missing somewhere in the above, for example DATEDIFF(today, today) if today is a weekend will return -1 instead of returning 0.

score 1 · Answer 3 · answered Apr 26 '12 at 10:15

1

Something like this may work. Add all holiday dates and weekend dates to a table.

SELECT 
  DATEDIFF(end_date, start_date) 
FROM table
WHERE date NOT IN (SELECT date FROM holidaydatestable )

answered Apr 26 '12 at 10:15

Robbo_UK

11,351
25
81
117

But, how are you going to know weekend date, this is not possible, I guess – Akram Apr 26 '12 at 10:22
you add the weekend dates to the holidaydatestable – Robbo_UK Apr 26 '12 at 10:33
how can you know all weekend for the first 10 years – Akram Apr 26 '12 at 10:36

score 1 · Answer 4 · answered Aug 14 '14 at 10:49

1

Try This Code this will Calculate no of days Excluding Weekends

 SELECT
       (DATEDIFF(dd, @StartDate, @EndDate)+1)
      -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
from test_tbl where date NOT IN (SELECT date FROM holidaydatestable )

answered Aug 14 '14 at 10:49

Tanmay Nehete

2,138
4
31
42

score 0 · Answer 5 · answered Apr 26 '12 at 09:47

0

Make a function that will make a while cycle between the dates incrementing the number of days when it's not a saturday or sunday.

answered Apr 26 '12 at 09:47

aF.

64,980
43
135
198

Is it possible to achieve this without using loops, I don't have any idea about that's why I stack in this point. I was interesting if I can use just certain library to count it? – Akram Apr 26 '12 at 09:58

How to count date difference excluding weekend and holidays in MySQL

5 Answers5

Linked