生成全年日期并

最新推荐文章于 2024-08-28 11:17:44 发布

早起鸟儿

最新推荐文章于 2024-08-28 11:17:44 发布

阅读量143

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qswdcs1/article/details/112009085

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

该博客介绍了如何使用SQL创建一个全年日期表`year_date_all`，包括日期、年份、月份、天数和周数等字段，并定义了`db_status`字段以区分工作日、周末和法定假日。此外，还提供了一个存储过程`getAllYearDateFunc`，用于根据输入年份动态生成表数据，考虑了平闰年的判断。

摘要由CSDN通过智能技术生成

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：法定休息日',
    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),
(
select case when date_format(startDay,'%w') in (0,6) then 1 else 0 end
)
);
 SET count=count+1;
 SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
END WHILE;

END IF;
END

文章借鉴文章借鉴