0

I copied some MySQL code from a website for creating a date dimension table, and I've tried to run it on MySQL instance in PhpMyAdmin, but I'm getting an error. The code is as follows:

CREATE TABLE IF NOT EXISTS datedim  (
    date_id INT NOT NULL auto_increment,
    fulldate date,
    dayofmonth int,
    dayofyear int,
    dayofweek int,
    dayname varchar(10),
    monthnumber int,
    monthname varchar(10),
    year    int,
    quarter tinyint,
    PRIMARY KEY(date_id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;


delimiter //

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

    DELETE FROM datedim;

    SET v_full_date = p_start_date;
    WHILE v_full_date < p_end_date DO

        INSERT INTO datedim (
            fulldate ,
            dayofmonth ,
            dayofyear ,
            dayofweek ,
            dayname ,
            monthnumber,
            monthname,
            year,
            quarter
        ) VALUES (
            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;
END;

It was posted in 2009 so don't know what the issue is (maybe delimiter?).

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE) BEGIN D' at line 2

https://tech.akom.net/archives/36-Creating-A-Basic-Date-Dimension-Table-in-MySQL.html

I have tried changing the delimiter in the phpmyadmin sql query to be // and removing the delimiter line in the sql code, but now I get:

2 errors were found during analysis.

Unexpected beginning of statement. (near "p_start_date" at position 415)
Unrecognized statement type. (near "DATE" at position 428)
Suzan Aydın
  • 355
  • 6
  • 17

1 Answers1

2

You have changed the delimiter to // so you need to use // not ;

Either

delimiter //

DROP PROCEDURE IF EXISTS datedimbuild//
CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)
BEGIN
  ...

or

DROP PROCEDURE IF EXISTS datedimbuild;

delimiter //
CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)
BEGIN
  ...

Have a look at this previous question delimiters-in-mysql

Mark B
  • 649
  • 5
  • 11