Migrating dates and datetimes that are set to 0000-00-00 or 0000-00-00 00:00:00 in MySql / MariaDB.

You may have encountered it: in older versions of MySql it was commonplace that an undefined date would be set automatically to 0000-00-00 and an undefined datetime to 0000-00-00 00:00:00 by the database engine.
The peculiar thing is that a tabledump from one of those older database will import just fine in a modern version of MySql or MariaDB, but as soon as you want to do anything with the data, you will encounter a problem:
Lets assume you import the leggacy data into the database 'import' and after the dump-restore it will be transferred to the new target database using sql statements. The target database is called 'target' in this example. (Not very creative, but pretty clear.)
If you have a table with a datefield in it that has values in it that are set to 0000-00-00 you will encounter the following:
TRUNCATE target.user_log;
INSERT INTO target.user_log 
SELECT * 
FROM import.user_log;

 Error in SQL query:

INSERT INTO target.user_log SELECT * from import.user_log

MySQL said: Documentation
#1292 - Incorrect date value: '0000-00-00' for column 'logindatum' at row 26171

Lets try to intercept those illegal dates.
Now, unfortunately when transforming data inserted from a select you will have to name all columns you select from, so the queries can become unwieldy. Personally I usually select the names directly from the table-create statement, but that is beyond the scope of this post. Suffice to say you need to use the exact same columnnames in the select as will be inserted, and in the same order. 
TRUNCATE target.user_log;
INSERT INTO target.user_log 
SELECT id, user_id, ip, CASE WHEN logindatum ='0000-00-00' THEN '0000-01-01' ELSE logindatum END, logintime 
FROM import.user_log

 Error in SQL query:

INSERT INTO target.user_log 
SELECT id, user_id, ip, CASE WHEN logindatum ='0000-00-00' THEN '0000-01-01' ELSE logindatum END, logintime 
FROM import.user_log

MySQL said: Documentation
#1292 - Incorrect date value: '0000-00-00' for column 'logindatum' at row 1

You can try any variation on this, but the problem is this: '0000-00-00' is no longer seen as a valid date (!). This means that no matter how you try it, any comparisons on a datefield that contains this value will fail!
To get around this issue the only working solution is to convert the wonky date into a string, and do a comparison with it, like so: 
TRUNCATE target.user_log;
INSERT INTO target.user_log 
SELECT id, user_id, ip, CASE WHEN CAST(logindatum AS char(10)) = '0000-00-00' THEN '0000-01-01' ELSE logindatum END, logintime 
FROM import.user_log
It is immediately visible what is the problem: the year 0000 is fine, but month 00 and day 00 are not appreciated anymore. The CASE WHEN statement fixes the issue by hard-converting dates that are stored as '0000-00-00' into a legal date, namely '0000-01-01'.
For Datetime columns a very similar aproach can be used: 
TRUNCATE target.user_log;
INSERT INTO target.some_log 
SELECT id, CASE WHEN CAST(some_datetime AS char(19)) = '0000-00-00 00:00:00' THEN '0000-01-01 00:00:00' ELSE some_datetime END 
FROM import.some_log
Note the longer char field: it needs to be 19 characters to also hold the time, still only the month and day is changed.

This transformation will allow all date and datetime fields to be properly imported. Fixed?
Well, there is a problem, one that is not so easy to overcome. If the connected application (with old MySql database those often are ditto old PHP applications) is relying on the 0000-00-00 value in the code, this must be changed in the actual code!
Not ideal, but consider that punishment for using legacy stuff.



Ellert, January 2021.
img