useful-qry/function-in-db/procedures/common/file_cal_format.sql

DROP PROCEDURE IF EXISTS fill_cal_format;
DELIMITER |
CREATE PROCEDURE fill_cal_format(dateStart DATE, dateEnd DATE)
SQL SECURITY INVOKER
BEGIN
  declare i date ;
  declare w int;

  set i = dateStart ;
  WHILE i <= dateEnd DO

    set w = week(i);
  	IF weekday(i) = 0 THEN
    	INSERT INTO dbr_cal_format(date_type, date_index)
    	VALUES ('week', concat( DATE_FORMAT(dateStart, '%y.%m'), '-', if(length(w) = 1, concat('0',w), w) ));
    END IF;

    IF DATE_FORMAT(i, '%d') = '01' THEN
    	INSERT INTO dbr_cal_format(date_type, date_index)
    	VALUES ('month', DATE_FORMAT(i, '%y.%m'));
    END IF;

    INSERT INTO dbr_cal_format(date_type, date_index)
    VALUES ('day', DATE_FORMAT(i, '%Y%m%d'));
    SET i = date_add(i, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL fill_cal_format('2021-01-01','2021-12-31');