数据库存入年月日、星期、节假日

ORACLE

1.建表语句

CREATE TABLE "T_WEEKJUDGE" (
"DATEDATA" VARCHAR2(10 BYTE) NOT NULL ,
"WEEK" NUMBER(1) NOT NULL ,
"HOLIDAY" NUMBER DEFAULT 0  NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON COLUMN "T_WEEKJUDGE"."DATEDATA" IS '日期';
COMMENT ON COLUMN "T_WEEKJUDGE"."WEEK" IS '星期';
COMMENT ON COLUMN "T_WEEKJUDGE"."HOLIDAY" IS '节假日（1代表节假日2代表因节假日补上班）';

2.存储过程

CREATE OR REPLACE 
PROCEDURE "WEEK_JUDGE"
AS
BEGIN 
FOR i in 0 .. 20000 LOOP
	INSERT INTO T_WEEKJUDGE(DATEDATA,WEEK) VALUES (TO_CHAR(SYSDATE+i,'YYYY-MM-DD'),TO_CHAR(SYSDATE+i,'D'));
END LOOP;
END;

3.备注内容

查询语句：查看两个日期间的天数。

select floor(to_date('20681004','yyyymmdd') - to_date('20140101','yyyymmdd')) from dual; 

注：可修改系统日期变更SYSDATE。

MYSQL

1.建表语句

CREATE TABLE `DATETEST` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT,
	`DATE` CHAR(10) NOT NULL,
	`WEEK` INT(1) NOT NULL,
	`HOLIDAY` TINYINT(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1;

2.存储过程

drop procedure if exists datetest; 
create procedure datetest()
begin 
declare i int;
declare oneday date;
set i = 0;

repeat 
	select DATE_ADD(date_format(curdate(),'%Y-%m-%d'),INTERVAL i DAY) into oneday from dual;
	insert into datetest(date,week) VALUES (oneday,date_format(oneday,'%w'));
	set i = i + 1;
until i >= 20000

end repeat;
end;

后续工作

将数据表中的HOLIDAY字段修改为1或2即可。
如图结果：

实用场景

可在数据库中控制工作日、非工作日的判断。