MySQL Incorrect datetime value: ‘0000-00-00 00:00:00’

by

I wasn’t able to do this:

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(on MySQL 5.7.13).

I kept getting the Incorrect datetime value: '0000-00-00 00:00:00' error.

Strangely, this worked: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. I have no idea why the former fails and the latter works… maybe a MySQL bug?

At any case, this UPDATE query worked:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'

More Related Contents:

  1. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  2. How to import large sql file in phpmyadmin
  3. How to figure out size of Indexes in MySQL
  4. How do you force mysql LIKE to be case sensitive? [duplicate]
  5. MySQL Alter table causes Error: Invalid use of NULL value
  6. Delete all rows with timestamp older than x days
  7. MySQL – Cannot insert NULL value in column, but I have a default value specified?
  8. Xampp-mysql – “Table doesn’t exist in engine” #1932
  9. Unserialize through query at database level itself
  10. Detecting utf8 broken characters in MySQL
  11. How can I stop a MySQL query if it takes too long?
  12. MySQL throws Incorrect string value error
  13. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  14. Equivalent of MSSQL IDENTITY Column in MySQL
  15. #1060 – Duplicate column name ‘id’
  16. In what order are MySQL JOINs evaluated?
  17. Count duplicates records in Mysql table?
  18. Update multiple rows with multiple ‘where’ clauses for each individual row
  19. uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)
  20. Migrating data into Firebase from MySQL
  21. ORDER BY alphabet first then follow by number
  22. Inventory management with stock options
  23. How to escape apostrophe (a single quote) in MySql?
  24. django cannot connect mysql in docker-compose
  25. WHERE datetime older than some time (eg. 15 minutes)
  26. mysql create table dynamically
  27. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
  28. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)
  29. Working with INTERVAL and CURDATE in MySQL
  30. Errno 121, duplicate key on write or update?

Leave a Comment