- BEGIN
- DECLARE num int DEFAULT -1;
- CREATE TEMPORARY TABLE under_日历 (
- yyyy int,
- q int,
- mm int,
- dd VARCHAR(15),
- w INT,
- wd VARCHAR(15)
- );
- lp:LOOP
- set num = num+1;
- insert into under_日历
- select year(CURDATE()) as y,
- floor((date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%m')+2)/3) as q,
- date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%m') as M,
- date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%Y-%m-%d') as date,
- date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%u') as W,
- case DAYOFWEEK(date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%Y-%m-%d'))-1
- when 1 then '星期一'
- when 2 then '星期二'
- when 3 then '星期三'
- when 4 then '星期四'
- when 5 then '星期五'
- when 6 then '星期六'
- when 0 then '星期日'
- end as DW;
- if num=dayofyear(concat(YEAR(now()),'-12-31'))-1 THEN LEAVE lp;
- END if;
- END LOOP;
- select * from under_日历;
- DROP TABLE under_日历;
- END
复制代码 刚刚看到个帖子是用帆软来做日历的(http://bbs.fanruan.com/thread-122813-1-1.html),然后就想起来自己以前做过类似的,直接用sql生成本年日历
直接放在存储过程里运行年,季度,月,日,周,星期就都出来了
编辑于 2019-1-17 10:57
|