CODEWITHSUNDEEP
mysql
user3304303
user3304303

Reputation: 1043

How do you update NULL date fields to have 0000-00-00 in PHP/MYSQL?

I am trying to update date fields that are NULL to have "0000-00-00" in them. I am trying:

UPDATE `results` SET `date_of_birth` = '0000-00-00' WHERE `date_of_birth` IS NULL

But when I simulate it, it says 0 matched records. However, if I run this, it brings back 31 records:

SELECT * FROM `results` WHERE `date_of_birth` IS NULL

Seems to be contradiction, or I am just missing something.

Any ideas?

Upvotes: 0

Views: 398

Answers (2)

A.D.
A.D.

Reputation: 2372

You can not use any of the statement to update this which not contain a proper date value if this column has type date.

UPDATE `results` SET `date_of_birth` = '' WHERE `date_of_birth` = '' or cast(`date_of_birth` as date) is null or `date_of_birth` is null;

#With no strict mode.

UPDATE `results` SET `date_of_birth` = '0000-00-00' WHERE `date_of_birth` = '' or cast(`date_of_birth` as date) is null or `date_of_birth` is null;

Upvotes: 1

Sanu Khan
Sanu Khan

Reputation: 100

Check your sql.mode

To Check MYSQL mode

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

Refer

Upvotes: 0

Related Questions