SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE DateDimension ( DateID INT PRIMARY KEY AUTO_INCREMENT, Date DATE, Day INT, Month INT, Year INT, MonthName VARCHAR(15), Quarter INT, IsWeekend BOOLEAN ); DELIMITER // CREATE PROCEDURE PopulateDateDimension() BEGIN DECLARE start_date DATE; DECLARE end_date DATE; SET start_date = '2006-01-01'; SET end_date = '2016-12-31'; WHILE start_date <= end_date DO INSERT INTO DateDimension (Date, Day, Month, Year, MonthName, Quarter, IsWeekend) VALUES ( start_date, DAY(start_date), MONTH(start_date), YEAR(start_date), MONTHNAME(start_date), QUARTER(start_date), DAYOFWEEK(start_date) IN (1, 7) ); SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY); END WHILE; END // DELIMITER ; -- Execute the stored procedure CALL PopulateDateDimension();