Question 63948 in PhpMyAdmin Question posted in
The official documentation can be found here.

How do I fix this MYSQL//Phpmyadmin error?

So I tried to do a ALTER TABLE command to my table to add a date column that stores what time a post was made. Whenever I enter the SQL code, it pops up this error in PhpMyAdmin. I’m a beginner and I would really like if someone could help me.

Original code:

ALTER TABLE posts 
ADD date datetime not null;

Error that pops up:
#1292 – Incorrect date value: ‘0000-00-00’ for column ‘website’ . ‘posts’ . ‘date’ at row 1

Tags:

2

Answers


  1. 0

    Give a default value

    ALTER TABLE posts ADD `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

    Or if default value is not desired, add the column allowing NULL, update with appropriate values, and change the column to NOT NULL

    ALTER TABLE posts ADD `date` datetime
;
UPDATE posts 
SET `date` = NOW() -- or any suitable values
;
ALTER TABLE posts CHANGE `date` `date` datetime NOT NULL
;
    Login or Signup to reply.
  2. 0

    You are adding a column, that can’t be null. So what value do the existing rows get?

    You need to either specify a default value, or allow null until its populated somehow.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search