BEGIN
DECLARE dayCount int(6) DEFAULT 0;
DECLARE otherDay int(6) DEFAULT 0;
DECLARE theweek int(6) DEFAULT 0;
set dayCount = FLOOR(timestampdiff(day,DATE_SUB(fromD,INTERVAL 1 DAY),toD)/7)*5;
set otherDay = timestampdiff(day,DATE_SUB(fromD,INTERVAL 1 DAY),toD)%7;
set theweek = WEEKDAY(fromD);
if otherDay = 1 THEN
if theweek = 0 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+1;
end IF;
ELSEIF otherDay = 2 THEN
IF theweek = 0 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 5 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+2;
END IF;
ELSEIF otherDay = 3 THEN
IF theweek = 0 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 4 THEN
set dayCount = dayCount+1;
ELSEIF theweek = 5 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+3;
END IF;
ELSEIF otherDay = 4 THEN
IF theweek = 0 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 4 THEN
set dayCount = dayCount+2;
ELSEIF theweek = 5 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+4;
END IF;
ELSEIF otherDay = 5 THEN
IF theweek = 0 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 4 THEN
set dayCount = dayCount+3;
ELSEIF theweek = 5 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+5;
END IF;
ELSEIF otherDay = 6 THEN
IF theweek = 0 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 1 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 2 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 3 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 4 THEN
set dayCount = dayCount+4;
ELSEIF theweek = 5 THEN
set dayCount = dayCount+5;
ELSEIF theweek = 6 THEN
set dayCount = dayCount+5;
END IF;
END IF;
RETURN dayCount;
END
mysql 一种比较笨的计算两个日期之间相差的工作日数,支持跨年计算的方法(仅排除周六周日)
最新推荐文章于 2022-11-25 14:33:11 发布