error while trying to change a table by adding a new column of type datetime in mysql

Question

ALTER TABLE tb_coletas ADD dia DATETIME NOT NULL

generates the following error

Incorrect datetime value: '0000-00-00 00:00:00' for column

I didn’t understand the error, as far as I know everything is normal

Answer 1

How you created a column that is NOT NULL, it has to have some value. As you did not indicate the value default, is used the "implicit default value", which in this case is "zero".

Then you’d have to put some value default for this column. In the case, you can choose the current date:

ALTER TABLE tb_coletas ADD dia DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Or some fixed amount:

-- 21 de janeiro de 2020, às 10:30
ALTER TABLE tb_coletas ADD dia DATETIME NOT NULL default '2020-01-21 10:30:00';

---

Or, if the date can be empty, then remove the NOT NULL.