创建一个查询工作日天数的函数
DROP FUNCTION IF EXISTS fun_workdaynum;
CREATE FUNCTION fun_workdaynum(start_date Date, end_date DATE)
RETURNS INT(20)
BEGIN
DECLARE daynum INT(20) DEFAULT 1;
IF start_date > end_date then
RETURN -1;
END IF;
SET daynum =
case
when week(end_date)-week(start_date) = 0 then -- 同个星期
dayofweek(end_date) - dayofweek(start_date) + 1
- case
when (dayofweek(start_date) > 1 and dayofweek(end_date) < 7) then 0
when (dayofweek(start_date) = 1 and dayofweek(end_date) =7) then 2
else 1
end
else (52*(year(end_date)-year(start_date)-1)+(52-week(start_date))+week(end_date)-1)*5 -- 跨星期/跨年份
+ case
when dayofweek(start_date) = 1 then 5
when dayofweek(start_da