SQL实现2个日期之间的工作日数(MySQL)
限制条件:只能计算同一年的工作日,如果您需要跨年计算,请输入2次即可。
雕虫小技,希望能对你有所帮助。
DROP FUNCTION IF EXISTS `workdaynum`;
DELIMITER $$
CREATE FUNCTION `workdaynum`(`datefrom` date,`dateto` date)
RETURNS int(20) NO SQL
BEGIN
declare days int default 1;
if (datefrom > dateto or year(datefrom) != year(dateto)) then
return -1;
end if;
set days =
case
when week(dateto)-week(datefrom) = 0 then
dayofweek(dateto) - dayofweek(datefrom) + 1
- case
when (dayofweek(datefrom) > 1 and dayofweek(dateto) < 7) then 0
when (dayofweek(datefrom) = 1 and dayofweek(dateto) =7) then 2
else 1
end
else (week(dateto)-week(datefrom)-1) * 5
+ case
when dayofweek(datefrom) = 1 then 5
when dayofweek(datefrom) = 7 then 0
else 7 - dayofweek(datefrom)
end
+ case
when dayofweek(dateto) = 1 then 0
when dayofweek(dateto) = 7 then 5
else dayofweek(dateto) - 1
end
end;
return days;
end$$
DELIMITER ;
测试: