0

There are two Datetime column in my table both having default value as "0000-00-00 00:00:00"enter image description here

When I was trying to change some structure(which is not related to either of these columns)of the table, an #1067 error shows up saying `invalid default value for 'product_date_added'.

Question #1 Why a datetime column's default value can't be "0000-00-00 00:00:00"?

Then I tried to change the default value of product_date_added, and here is what I got: enter image description here

As shown in the picture, #1067 shows up for the other datetime column. And this goes vice versa, I am dumbfounding.

I tried this with other tables and they all have this issue: when trying to alter table structure, an #1067 error shows up for a datetime column whose default value is "0000-00-00 00:00:00".

I also tried to set the default value to a real datetime value from command shell, here is what I got: enter image description here As you can see, the 1067 error still shows up for the other column. Why these two column are affecting each other?

I also tried this: and this: enter image description here

Question #2: How to solve this issue?

shenkwen
  • 3,536
  • 5
  • 45
  • 85
  • did you try updating that column with NULL (only rows with zero date time value) and then set default value null or changing the structure ? – Deepansh Sachdeva Sep 13 '17 at 14:30
  • Won't allow me to update it with null, says "please enter a valid date or time" – shenkwen Sep 13 '17 at 14:39
  • A way around to handle such problem is to choose a certain valid date time value eg. '2020-01-01 00:00:00' and update those rows with zero date value with that date hardcoded value, then you can set that column to default null and again check for that date value and update with null. Try it, if possible. – Deepansh Sachdeva Sep 13 '17 at 14:45
  • I tried setting default value to a real datetime, but it also won't allow me, showing the 1067 error for the other column too. – shenkwen Sep 13 '17 at 14:51
  • Run a sql query to update those zero date time values with dummy date value. – Deepansh Sachdeva Sep 13 '17 at 14:55
  • I've edited the question with an additional screenshot.@DeepanshSachdeva – shenkwen Sep 13 '17 at 15:13
  • `UPDATE table SET column = '2020-01-01 11:11:11' WHERE column = '0000-00-00 00:00:00'` run an update query, not alter table – Deepansh Sachdeva Sep 13 '17 at 15:20
  • Thanks. I tried please see the editted question for the screenshot. – shenkwen Sep 13 '17 at 15:34
  • Possible duplicate of https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 – Deepansh Sachdeva Sep 13 '17 at 15:44
  • 1
    @DeepanshSachdeva Thanks. I somehow get the answer from the question you post. – shenkwen Sep 13 '17 at 17:04

1 Answers1

0

1) The database won't accept a datetime value of "0000-00-00 00:00:00" because the the default sql_mode includes NO_ZERO_DATE and NO_ZERO_IN_DATE

2) To solve the issue, the mode of mysql needs to be changed, which can be done in my.cnf file by adding

[mysqld]
sql_mode = "modes"

where modes is a string. To get the mode string, one needs to get the original string by running "select @@sql_mode" and truncate the two modes string from it.

shenkwen
  • 3,536
  • 5
  • 45
  • 85