MySQL 5.7 set datetime or date to NULL from 0000-00-00 00:00:00 - Fixes incorrect datetime value: '0000-00-00 00:00:00' (without NO_ZERO_DATE workaround)

mysql57_convert_datetime_null.sql

/** ---
 * To set an existing column in MySQL 5.7 from 0000-00-00 00:00:00 to NULL as default
 * the following iterative 3 steps will get the job done.
 * (Without just using the NO_ZERO_DATE compatibility mode-workaround)
 --- */

/* Step 1) Set all 0000-00-00... to a valid but nonsense value: */
UPDATE my_table SET my_datetime_col = '1001-01-01 00:00:00' WHERE CAST(my_datetime_col AS CHAR(20)) = '0000-00-00 00:00:00';

/* Step 2) Modify the column to DEFAULT: NULL */
ALTER TABLE my_table MODIFY COLUMN my_datetime_col DATETIME NULL;

/* Step 3) Revert the valid nonsense value to NULL */
UPDATE my_table SET my_datetime_col = NULL WHERE my_datetime_col = '1001-01-01 00:00:00';

mysql57_convert_datez_null.sql

/** ---
 * For DATE Columns
 */
/* Step 1) Set all 0000-00-00... to a valid but nonsense value: */
UPDATE my_table SET my_date_col = '1001-01-01' WHERE CAST(my_date_col AS CHAR(10)) = '0000-00-00';

/* Step 2) Modify the column to DEFAULT: NULL */
ALTER TABLE my_table MODIFY COLUMN my_date_col DATE NULL;

/* Step 3) Revert the valid nonsense value to NULL */
UPDATE my_table SET my_date_col = NULL WHERE my_date_col = '1001-01-01';