①同一年的情况下计算工作日函数
CREATE DEFINER=`root`@`%` FUNCTION `WORKDAYSONEYEAR`(`datefrom` datetime,`dateto` datetime) RETURNS int(20)
NO SQL
BEGIN
declare days int default 1;
# 如果起始时间大于结束时间或者日期跨年那么直接返回-1,表示不支持
if (datefrom > dateto or year(datefrom) != year(dateto)) then
return -1;
end if;
set days =
case
# 同一周的情况:计算时间间隔再减去周六周日的天数
# 每周开始时间为星期日,1是星期日 7是星期六
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
#不是同一周的情况:间隔周数 * 5 加上同一周的工作日
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
②计算跨一年的情况
本质还是使用上面的函数。比如计算-12-23到-01-10的工作日,可以使用上面的函数分别计算-12-25到20 9-12-31和-01-01到-01-10的工作日,取两者之和即可。
CREATE DEFINER=`root`@`%` FUNCTION `WORKDAYSTWOYEARS`(`startdate` datetime,`enddate` datetime) RETURNS int(20)
BEGIN
#起始时间大于结束时间,直接返回-1,表示不支持
if (startdate > enddate) then
return -1;
#同一年的情况下,直接使用上面的WORKDAYSONEYEAR()函数计算
ELSEIF (year(startdate) = year(enddate)) then
set @days = WORKDAYSONEYEAR(startdate,enddate);
return @days;
#年份相差一年,分两段进行处理
ELSEIF (year(startdate) < year(enddate)) then
set @yearofstartdate = year(startdate);
set @yearofenddate = year(enddate);
set @lastdayofstartdate = CONCAT(@yearofstartdate,'-12-31');
set @intervelone = workdaynum(startdate,@lastdayofstartdate);
set @days = @intervelone;
set @firstdayofenddate = CONCAT(@yearofenddate,'-01-01');
set @interveltwo = workdaynum(@firstdayofenddate,enddate);
set @days = @intervelone + @interveltwo;
end if;
return @days;
end
测试:
select WORKDAYS('-12-15','-01-05');