*--* 计算两个日期工作日 不包含跨年的
DROP FUNCTION IF EXISTS `workdaynum`;
DELIMITER $$
CREATE FUNCTION `WORKDAY`(`startdate` date,`enddate` date)
RETURNS int(20) NO SQL
BEGIN
#起始时间大于结束时间,直接返回-1,表示不支持
if (startdate > enddate) then
return -1;
#同一年的情况下,直接使用上面的WORKDAYSONEYEAR()函数计算
ELSEIF (year(startdate) = year(enddate)) then
set @days = workdaynums(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 = workdaynums(startdate,@lastdayofstartdate);
set @days = @intervelone;
set @firstdayofenddate = CONCAT(@yearofenddate,'-01-01');
set @interveltwo = workdaynums(@firstdayofenddate,enddate);
set @days = @intervelone + @interveltwo;
end if;
RETURN @days;
end$$
DELIMITER ;
*--* 包含跨年
DROP FUNCTION IF EXISTS `workdaynum`;
DELIMITER $$
CREATE FUNCTION `WORKDAYS`(`startdate` date,`enddate` date)
RETURNS int(20) NO SQL
BEGIN
#起始时间大于结束时间,直接返回-1,表示不支持
if (startdate > enddate) then
return -1;
#同一年的情况下,直接使用上面的WORKDAYSONEYEAR()函数计算
ELSEIF (year(startdate) = year(enddate)) then
set @days = workdaynum(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$$
DELIMITER ;