Created
March 23, 2021 20:19
-
-
Save Swiss-Mac-User/20840259c132b707cba249fd4c8f5855 to your computer and use it in GitHub Desktop.
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** --- | |
* 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'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** --- | |
* 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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment