DROP PROCEDURE
IF EXISTS pro_dim_date;
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 datetemplate (
`date_id`,
`date_name`,
`day_id`,
`year_id`,
`month_id`,
`day_of_month`
)
VALUES
(
DATE_FORMAT(var, '%Y%m%d'),
DATE_FORMAT(var, '%Y-%m-%d'),
DAYOFMONTH(var),
YEAR (var),
MONTH (var),
CONCAT(
MONTH (var),
'/',
DAYOFMONTH(var)
)
);
SET var = DATE_ADD(var, INTERVAL 1 DAY);
END
WHILE;
END
IF EXISTS pro_dim_date;
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 datetemplate (
`date_id`,
`date_name`,
`day_id`,
`year_id`,
`month_id`,
`day_of_month`
)
VALUES
(
DATE_FORMAT(var, '%Y%m%d'),
DATE_FORMAT(var, '%Y-%m-%d'),
DAYOFMONTH(var),
YEAR (var),
MONTH (var),
CONCAT(
MONTH (var),
'/',
DAYOFMONTH(var)
)
);
SET var = DATE_ADD(var, INTERVAL 1 DAY);
END
WHILE;
END