- DROP PROCEDURE IF EXISTS pro_dim_date;
- tudou@Gyyx
- CREATE PROCEDURE pro_dim_date(IN bdate DATE,IN edate DATE)
- BEGIN
- DECLARE var DATE DEFAULT bdate;
- DECLARE evar DATE DEFAULT DATE_ADD(edate,INTERVAL 1 DAY);
- DECLARE bweek DATE;
- DECLARE eweek DATE;
- WHILE var<evar DO
- SET bweek = DATE_ADD(DATE_SUB(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);
- SET eweek = DATE_SUB(DATE_ADD(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);
- INSERT INTO gyyx_report.dim_date
- (
- `date_id`,
- `date_name`,
- `date_of_month`,
- `year_id`,
- `year_name`,
- `quarter_id`,
- `quarter_name`,
- `month_id`,
- `month_name`,
- `month_of_year_name`,
- `month_of_year_id`,
- `week_id`,
- `week_name`,
- `week_of_year_id`,
- `week_of_year_name`,
- `is_weekend`
- )
- VALUES
- (
- DATE_FORMAT(var,'%Y%m%d'),
- DATE_FORMAT(var,'%Y-%m-%d'),
- DAYOFMONTH(var),
- YEAR(var),
- CONCAT(YEAR(var),'年'),
- QUARTER(var),
- CONCAT(QUARTER(var),'季度'),
- DATE_FORMAT(var,'%Y%m'),
- CONCAT(YEAR(var),'年',MONTH(var),'月'),
- CONCAT(MONTH(var),'月'),
- MONTH(var),
- WEEKDAY(var),
- CASE WEEKDAY(var) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN 2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六' WHEN 6 THEN '星期日' END,
- WEEKOFYEAR(var),
- CONCAT('第',WEEKOFYEAR(var),'周(',MONTH(bweek),'月',DAY(bweek),'日~',MONTH(eweek),'月',DAY(eweek),'日'),
- CASE WHEN WEEKDAY(var)>4 THEN '是' ELSE '否' END
- );
- SET var=DATE_ADD(var,INTERVAL 1 DAY);
- END WHILE;
- END
|
评论暂时关闭