I need to change a few values on my DB.
I forgot to set nullable to the table and it set to 0000-00-00 00:00:00 by default.
Now I need to convert that value in NULL
.
The field type is Datetime.
How can I do it?
I try with the typical Update table set field = NULL WHERE field = '0000-00-00 00:00:00';
but it doesn't work.
"NULL" can be specified as a value in the Date field to get an empty/blank by using INSERT statement. Example: CREATE table test1 (col1 date); INSERT into test1 values (NULL);
DateTime CAN be compared to null; It cannot hold null value, thus the comparison will always be false. DateTime is a "Value Type". Basically a "value type" can't set to NULL. But by making them to "Nullable" type, We can set to null.
The . NET DateTime data type cannot handle NULL values.
DateTime itself is a value type. It cannot be null.
You need to first make the column nullable:
@Mysql5.7
Wrong :
update episodes set `ending` = NULL WHERE `ending` = '0000-00-00'
Correct :
update episodes set `ending` = NULL WHERE `ending` = 0000-00-00
Note: Remove the quote from the date value in your where clause. Hope it help someone
From MySQL 5.7, SQL mode NO_ZERO_DATE makes this update impossible unless you firstly disable this restriction (for the duration of the transaction only).
SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", ""));
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';
You need to first make the column nullable:
ALTER TABLE mytable MODIFY COLUMN field DATETIME NULL;
And then update the values:
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With