Script to build a Date dimension for OLAP reporting on MySQL

MySqlDateDim

DROP TABLE IF EXISTS DimDate;
CREATE TABLE IF NOT EXISTS DimDate  (
    date_id  INT UNSIGNED NOT NULL,
    fulldate date,
    dayofmonth tinyint UNSIGNED,
    dayofyear smallint UNSIGNED,
    dayofweek tinyint UNSIGNED,
    dayname varchar(10),
    monthnumber tinyint UNSIGNED,
    monthname varchar(10),
    year    smallint UNSIGNED,
    quarter tinyint UNSIGNED,
    PRIMARY KEY(date_id)
) ENGINE=InnoDB;

delimiter //

DROP PROCEDURE IF EXISTS datedimbuild;
CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)
BEGIN
    DECLARE v_full_date DATE;

    DROP TABLE IF EXISTS  tempDate;
    
    CREATE TABLE tempDate  (
		date_id  INT UNSIGNED NOT NULL,
		fulldate date,
		dayofmonth tinyint UNSIGNED,
		dayofyear smallint UNSIGNED,
		dayofweek tinyint UNSIGNED,
		dayname varchar(10),
		monthnumber tinyint UNSIGNED,
		monthname varchar(10),
		year    smallint UNSIGNED,
		quarter tinyint UNSIGNED		
	) ENGINE=MEMORY;
	

    SET v_full_date = p_start_date;
    WHILE v_full_date < p_end_date DO

        INSERT INTO tempDate (
			date_id,
            fulldate ,
            dayofmonth ,
            dayofyear ,
            dayofweek ,
            dayname ,
            monthnumber,
            monthname,
            year,
            quarter
        ) VALUES (
			CAST(REPLACE(v_full_date,'-','') AS UNSIGNED),
            v_full_date,
            DAYOFMONTH(v_full_date),
            DAYOFYEAR(v_full_date),
            DAYOFWEEK(v_full_date),
            DAYNAME(v_full_date),
            MONTH(v_full_date),
            MONTHNAME(v_full_date),
            YEAR(v_full_date),
            QUARTER(v_full_date)
        );

        SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);
    END WHILE;

	INSERT INTO DimDate
	SELECT * FROM tempDate;

	DROP table tempDate;
END;
//