1

I have an admin users db, I want to create a column with type datetime for edits, to know when and admin acc was edited, I will introduce the time with php and MySQLi, im on phpmyadmin, when I try to create the column it says: 

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'editado' at row 1

MySQL code is: 

ALTER TABLE `admins` ADD `editado` DATETIME NOT NULL AFTER `password`;

Tried to execute on SQL but nothing, how can I do this?

rollstuhlfahrer
  • 3,988
  • 9
  • 25
  • 38
Jaume Sastre
  • 55
  • 1
  • 12
  • Is the table empty when you are adding the column? – Mic1780 Jan 22 '18 at 21:06
  • I think you might have wrongly set a default value to the datetime attribute. Can you add the code you are using? – cdaiga Jan 22 '18 at 21:13
  • You may need to provide a default value like `ALTER TABLE admins ADD editado DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER password;` – Stphane Jan 22 '18 at 22:38
  • Possible duplicate of [Set NOW() as Default Value for datetime datatype?](https://stackoverflow.com/questions/5818423/set-now-as-default-value-for-datetime-datatype) – Stphane Jan 22 '18 at 22:39

1 Answers1

2

If the table is not empty when you add a column, you need to first add the column as NULLABLE, update all the records in the table assigning a value to the column, then you can change the column you added from NULLABLE to NOT NULL. So something like this should be done:

ALTER TABLE admins ADD editado DATETIME NULL AFTER password;
UPDATE admins SET editado = '1900-01-01 00:00:00';
ALTER TABLE admins MODIFY editado DATETIME NOT NULL;

Not sure if the last query is correct syntax as I don't write MySQL often but thats what should happen.

Mic1780
  • 1,774
  • 9
  • 23
  • If i put your code, MySQL error: SQL query: ALTER TABLE admins ALTER COLUMN editado DATETIME NOT NULL MySQL said: Documentation #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 'DATETIME NOT NULL' at line 1. The last query is incorrect, can you fix it? – Jaume Sastre Jan 22 '18 at 21:20
  • Try `ALTER TABLE admins MODIFY editado DATETIME NOT NULL;`. That is correct syntax and should work. – Mic1780 Jan 22 '18 at 21:22