0

I get the error when trying to add a text column to an existing MySQL table.

The syntax I am using is:

ALTER TABLE tbl_user ADD COLUMN vBio TEXT AFTER vFirstName;

How can I alter the SQL syntax to get rid of this error?

1292 - Incorrect date value ’0000-00-00’ for column ‘dDob’ at row 4

Community
  • 1
  • 1
Michael Nares
  • 401
  • 4
  • 19
  • It's not quite a duplicate of that because I'm not trying to update a date, just to add a column. I've tried SET sql_mode = ''; and that didn't work. – Michael Nares Nov 28 '18 at 07:15
  • it works umpteenth times. Ensure that `set sql_mode = ''` is called in the same client session at the time of running `alter table` query. If you are using PHPMyAdmin, use both the queries together. – Madhur Bhaiya Nov 28 '18 at 07:16
  • You're getting the error on another column instead of the one you're adding? Weird. – Salman A Nov 28 '18 at 07:18
  • Indeed, it's a bit weird. – Michael Nares Nov 28 '18 at 07:18
  • 1
    @SalmanA it is not weird. SQL modes have been made stricter since 5.7 ; Fixing the `0000-00-00` dates values should be the first choice. – Madhur Bhaiya Nov 28 '18 at 07:20
  • Yeah... seems like it will ask you to fix the _other_ column before altering table... https://stackoverflow.com/questions/40133392/mysql-alter-table-add-column-error-at-another-column – Salman A Nov 28 '18 at 07:23
  • 2
    @MadhurBhaiya Running set sql_mode = '';ALTER TABLE tbl_user ADD COLUMN vBio TEXT AFTER vFirstName; in phpmyadmin as a single query did the trick like you say, many thanks. The issue was I was trying to run the two as separate queries, hence why it didn't work. – Michael Nares Nov 28 '18 at 07:24
  • @MichaelNares you should add that as the answer...then it's easy for others to search for in future. You are allowed to answer your own question (and accept your own answers), and others can vote for it then. – ADyson Nov 28 '18 at 09:10
  • OK, well I can't really do that now that the answer has been closed, but hopefully people should be able to find this anyway. – Michael Nares Dec 03 '18 at 07:03

0 Answers0