MySQL - Update old default datetime value to new default value

Question

I am working on an database version of mysql where they use to have a date time column with default value set as 0000-00-00 00:00:00. Now that i been creating new table with the new default date time value of 1000-00-00 00:00:00. When i try to INSERT and JOIN the two table i this error:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'process_date' at row 1

So to fix this i try to use this code

UPDATE tableA
SET process_date = '1000-01-01 00:00:00'
WHERE process_date = '0000-00-00 00:00:00';

And is giving me the same error as above, i have many rows and i don't want to manually set them.. any suggestion?

Added TableA (old table with old default date value)and TableB (new) have the same structure while TableA is the source. And i need data from TableA INSERT into TableB

AH going to try this: https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 — Photonic, Nov 15 '17 at 04:09
Possible duplicate of [MySQL Incorrect datetime value: '0000-00-00 00:00:00'](https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000) — User123456, Nov 15 '17 at 04:42

score 0 · Answer 1 · answered Nov 15 '17 at 04:17

0

In this post on stackoverflow MySQL Incorrect datetime value: '0000-00-00 00:00:00'

i used this code to fix my update problem

UPDATE TableA SET process_date = '1000-01-01 00:00:00' WHERE CAST(process_date AS CHAR(20)) = '0000-00-00 00:00:00';

answered Nov 15 '17 at 04:17

Photonic

1,316
19
31

MySQL - Update old default datetime value to new default value

1 Answers1