I've searched through many examples , good ones I got :
Count days between two dates, excluding weekends (MySQL only)
How to count date difference excluding weekend and holidays in MySQL
Calculate diffference between 2 dates in SQL, excluding weekend days
but didn't get most promising solution , so that i can use in my mysql-function for quering lakhs of rows.
This one was very new concept , but didn't worked for inputs like @start_date = '2013-08-03' , @end_date = '2013-08-21'
Expected ans : 13 , its giving only 12,
SELECT 5 * (DATEDIFF(@end_date, @start_date) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@start_date) + WEEKDAY(@end_date) + 1, 1);
So i'did tried to make it by myself -
Concept :
Input : 1. period_from_date - from date
2. period_to_date - to date
3. days_to_exclude - mapping : S M T W TH F Sat => 2^0 + 2^6
(sat and sun to exclude) ^ ^ ^ ^ ^ ^ ^
0 1 2 3 4 5 6
DELIMITER $$
USE `db_name`$$
DROP FUNCTION IF EXISTS `FUNC_CALC_TOTAL_WEEKDAYS`$$
CREATE DEFINER=`name`@`%` FUNCTION `FUNC_CALC_TOTAL_WEEKDAYS`( period_from_date DATE, period_to_date DATE, days_to_exclude INT ) RETURNS INT(11)
BEGIN
DECLARE period_total_num_days INT DEFAULT 0;
DECLARE period_total_working_days INT DEFAULT 0;
DECLARE period_extra_days INT DEFAULT 0;
DECLARE period_complete_weeks INT DEFAULT 0;
DECLARE extra_days_start_date DATE DEFAULT '0000-00-00';
DECLARE num_days_to_exclude INT DEFAULT 0;
DECLARE start_counter_frm INT DEFAULT 0;
DECLARE end_counter_to INT DEFAULT 6;
DECLARE temp_var INT DEFAULT 0;
# if no day to exclude return date-diff only
IF days_to_exclude = 0 THEN
RETURN DATEDIFF( period_to_date, period_from_date ) + 1 ;
END IF;
# get total no of days to exclude
WHILE start_counter_frm <= end_counter_to DO
SET temp_var = POW(2,start_counter_frm) ;
IF (temp_var & days_to_exclude) = temp_var THEN
SET num_days_to_exclude = num_days_to_exclude + 1;
END IF;
SET start_counter_frm = start_counter_frm + 1;
END WHILE;
# Get period days count
SET period_total_num_days = DATEDIFF( period_to_date, period_from_date ) + 1 ;
SET period_complete_weeks = FLOOR( period_total_num_days /7 );
SET period_extra_days = period_total_num_days - ( period_complete_weeks * 7 );
SET period_total_working_days = period_complete_weeks * (7 - num_days_to_exclude);
SET extra_days_start_date = DATE_SUB(period_to_date,INTERVAL period_extra_days DAY);
# get total working days from the left days
WHILE period_extra_days > 0 DO
SET temp_var = DAYOFWEEK(period_to_date) -1;
IF POW(2,temp_var) & days_to_exclude != POW(2,temp_var) THEN
SET period_total_working_days = period_total_working_days +1;
END IF;
SET period_to_date = DATE_SUB(period_to_date,INTERVAL 1 DAY);
SET period_extra_days = period_extra_days -1;
END WHILE;
RETURN period_total_working_days;
END$$
DELIMITER ;
Please let me know the holes where this would fail.Open to any suggestions and comments.
The DATEDIFF() function returns the number of days between two date values.
Discussion: To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure .
UPDATED: If you just need a number of weekdays between two dates you can get it like this
CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
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);
Note: The function will still work if you switch start date1
and end date2
dates.
Sample usage:
SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;
Output:
| WEEKDAYS1 | WEEKDAYS2 | ------------------------- | 13 | 13 |
Here is DBFiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With