Search code examples
phpmysqlsqldatabasezerofill

Beginner MySQL Syntax Error

I'm trying to create a table in an already created database. I've already switched into the database using USE church_fpip. Here's my code for creating the new table but for some reason it seems ZEROFILL is creating a syntax error.

mysql> CREATE TABLE sermons (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   year INT(4) NOT NULL,
    ->   month INT(2) NOT NULL ZEROFILL,
    ->   day INT(2) NOT NULL ZEROFILL,
    ->   title VARCHAR(50) NOT NULL,
    ->   preacher VARCHAR(30) NOT NULL,
    ->   length INT(3) NOT NULL,
    ->   visible TINYINT(1) NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
ERROR 1064 (42000): 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 'ZEROFILL,
  day INT(2) NOT NULL ZEROFILL,
  title VARCHAR(50) NOT NULL,
  preach' at line 4
Solution

  • I think ZEROFILL needs to be before NOT NULL such as

     month INT(2) ZEROFILL NOT NULL,
 day INT(2) ZEROFILL NOT NULL,

    However I would recommend the use of DATETIME for what you are trying to do, instead of three columns representing day/month/year.