/** ---
 * 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';