Mysql: Set 0000-00-00 to null

Question

I already found How to set to NULL a datetime with 0000-00-00 00:00:00 value? which is exactly my question - however, it does not work.

The colum 'bezahltDatum' is a date field which is nullable

The query

SELECT id,bezahltDatum FROM `officeBuch` WHERE  bezahltDatum = '0000-00-00'

gives 25 results and is working.

However, when I try to convert '0000-00-00' to null with

UPDATE `officeBuch` SET bezahltDatum = null WHERE bezahltDatum = '0000-00-00'

Then I get an error message

#1292 - Incorrect date value: '0000-00-00' for column 'bezahltDatum' at row 1

Why is this not working and how can I fix it? I am using phpMyAdmin Version 4.7.2 and Mysql 5.7.18-0ubuntu0.16.04.1

The only way i can think of is changing the SQL mode temporarily with something like this `SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';` before the actual update statement. — Brain Foo Long, Mar 03 '18 at 10:07
This is not my anwser but you can try this https://stackoverflow.com/questions/37292628/1292-incorrect-date-value-0000-00-00 — Bai Nguyen, Mar 03 '18 at 10:08
@BrainFooLong alright that does sounds good! Is the sql mode then permanently changed? Do I need to reset anything? — Adam, Mar 03 '18 at 10:12
@BrainFooLong it worked! If you write an answer I will check it. Thank you so much! — Adam, Mar 03 '18 at 10:14

score 3 · Accepted Answer · answered Mar 03 '18 at 10:32

3

The only way i can think of is changing the SQL mode temporarily with something like this

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

before the actual update statement. This will disable the strict mode temporarily and the error should not appear.

answered Mar 03 '18 at 10:32

Brain Foo Long

That works, but you say it will change "the SQL mode temporarily". What has to happen for the SQL mode to revert back to the way it was and why does it matter? – Steven J. Garner Aug 09 '22 at 14:56

1 Answers1