Step 1: Create Function compute (instart_dateVARCHAR (20), indate_countint) begindeclareiint; seti0; DELETEfromdim_day; values (DAY_ID, DAY_SHORT_DESC, DAY_LONG_DESC, WEEK_ID, WEEK
Step 1: create Function f_dim_day create procedure f_dim_day (in start_date VARCHAR (20), in date_count int) begin declare I int; set I = 0; DELETE from dim_day; while idate_count do INSERT into dim_day (DAY_ID, DAY_SHORT_DESC, DAY_LONG_DESC, WEEK_ID, WEEK
Step 1: Create Function f_dim_day
Create procedure f_dim_day (in start_date VARCHAR (20), in date_count int)
Begin
Declare I int;
Set I = 0;
DELETE from dim_day;
While I
INSERT into dim_day
(DAY_ID, DAY_SHORT_DESC, DAY_LONG_DESC, WEEK_ID, WEEK_LONG_DESC, MONTH_ID, Shard, MONTH_LONG_DESC, QUARTER_ID, QUARTER_LONG_DESC, YEAR_ID, YEAR_LONG_DESC)
SELECT
REPLACE (start_date, '-', '') DAY_ID,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y-% m-% D') DAY_SHORT_DESC,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y % m month % dday') DAY_LONG_DESC,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y % U') WEEK_ID,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y % u ') WEEK_LONG_DESC,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y % m') MONTH_ID,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y-% m') MONTH_SHORT_DESC,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y % m month') MONTH_LONG_DESC,
CONCAT (DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % y'), quarter (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s') QUARTER_ID,
CONCAT (DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % y'), 'Year ', quarter (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'), 'quarterly') QUARTER_LONG_DESC,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % y') YEAR_ID,
DATE_FORMAT (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'),' % Y year') YEAR_LONG_DESC
From dual;
Set I = I + 1;
Set start_date = DATE_FORMAT (date_add (STR_TO_DATE (start_date, '% Y-% m-% d % H: % I: % s'), interval 1 day ), '% Y-% m-% D ');
End while;
End;
Step 2: Call the function to input the start time and number of days and execute the following SQL statement:
Call f_dim_day ('2017-01-01 ', 2015)
The generated results are as follows: