mysql日期自动生成_excel自动识别周末和节假日

这里用存储过程生成全年日期、工作日与周末；再通过更新数据状态为节假日

• 创建日期表

   CREATE TABLE `year_date_all` ( `repDate` date NOT NULL COMMENT '日期', `repYear` int(8) NOT NULL COMMENT '年号', `repMonth` int(8) NOT NULL COMMENT '月', `repDay` int(8) NOT NULL COMMENT '天', `repWeek` int(8) NOT NULL COMMENT '周', `db_status` int(8) DEFAULT '0' COMMENT '0:工作日；1：周末休息日；2：法定休息日；3：调休工作日', PRIMARY KEY (`repDate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全年日期表'; 

• 存储过程插入数据

CREATE PROCEDURE `getAllYearDateFunc`(in yaerParam int(8)) BEGIN DECLARE count INT DEFAULT 0; DECLARE startDay DATE ; DECLARE yearNum INT DEFAULT 365; DECLARE workOrWeek INT DEFAULT 1; DECLARE yearBool INT DEFAULT 0; DECLARE db_status_ INT DEFAULT 0; // 判断是否数据存在 SELECT count(*) INTO yearBool FROM year_date_all WHERE repYear=yaerParam; IF yearBool=0 THEN SET startDay = DATE(concat(yaerParam,'-01-01')); //判断平闰年 IF yaerParam % 4 = 0 AND yaerParam % 100 <> 0 OR yaerParam % 400 = 0 THEN SET yearNum = 366; ELSE SET yearNum = 365; END IF; WHILE count < yearNum DO SET workOrWeek = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day)); IF (workOrWeek)>5 THEN SET db_status_=1; ELSE SET db_status_=0; END IF; INSERT INTO year_date_all(repDate,repYear,repMonth,repDay,repWeek,db_status) VALUES(startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay),db_status_); SET count=count+1; SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY); END WHILE; END IF; END 

• 效果图：