mysql-function计算两个日期之间的天数，不包括周末

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 MySQLCalculate diffference between 2 dates in SQL, excluding weekend daysbut 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 6DELIMITER $$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)BEGINDECLARE 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 onlyIF days_to_exclude = 0 THEN RETURN DATEDIFF( period_to_date, period_from_date ) + 1 ;END IF;# get total no of days to excludeWHILE 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 countSET 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 daysWHILE 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. 解决方案 UPDATED: If you just need a number of weekdays between two dates you can get it like thisCREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)RETURNS INTRETURN 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 这篇关于mysql-function计算两个日期之间的天数，不包括周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！