【无标题】mysql设置工作日

最新推荐文章于 2023-12-13 13:36:46 发布

洪之淇

最新推荐文章于 2023-12-13 13:36:46 发布

阅读量137

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_40320353/article/details/125989062

版权

drop PROCEDURE if exists `setWorkDay`;
CREATE  PROCEDURE `setWorkDay`(in yaerParam int(8))
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE workOrWeek INT DEFAULT 1;
DECLARE temp INT DEFAULT 1;
DECLARE startDay DATE ;
SET startDay = DATE(concat(yaerParam,'-01-01'));
 
 delete from year_date_all where year(repDate)=yaerParam;
WHILE year(startDay)=yaerParam DO
SET  workOrWeek = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day));
	 if  exists(select id from gov_holiday_plan where startDay>=开始日期 and startDay<=结束日期) THEN
	 set  temp=temp+1;
	 ELSE 
		 IF (workOrWeek<=5) THEN
				INSERT INTO year_date_all(repDate,db_status) VALUES(startDay,1);
		 END IF;
		 IF exists(select id from gov_holiday_plan where startDay=调休上班1 or startDay=调休上班2) THEN
				INSERT INTO year_date_all(repDate,db_status) VALUES(startDay,1);
		 END IF;		 
	 END IF;
 SET count=count+1;
 SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
END WHILE;
 
END