mysql fix invalid dates

Question

I have some invalid dates in my table. I've heard I can disable strict mode or use sql_mode ALLOW_INVALID_DATES which doesn't work (have confirmed with select @@global.sql_mode and restarted server).

My question is, can I not fix these 'invalid' dates? I'm not going to be able to disable strict mode on every server.

select * from table
>> Mysql2::Error: Invalid date: 1900-00-00

mysql --version
mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.1

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode   |
+---------------------+
| ALLOW_INVALID_DATES |

Answer 1

It looks like your error message is coming from your MySQL client, not the server. So, setting server strict mode isn't going to help you display these dates with this client.

It seems like you have some 2012-09-31 or 2013-02-29 style dates in your data. They're correctly formatted but otherwise are wrong. In pre 5.0.2 versions of MySQL these didn't get caught correctly going into your data. Now, your server, set to ALLOW_INVALID_DATES isn't gagging on them, but instead is converting them to '0000-00-00'. And the client is gacking on them.

Your first step to cleaning this up is to identify the offending rows. You could try this.

First, turn on ALLOW_INVALID_DATES.

Then, run this query to look around in your table. Don't use SELECT *.

  SELECT col,col,col,DATE_FORMAT(datecol,'%Y-%m-%d') 
    FROM mytable
   ORDER BY DATE_FORMAT(datecol,'%Y-%m-%d') 

Try to figure out from the result set which dates are garbage. They may be placed first in this select statement, but you're going to have to muck around a little bit to find them.

Next, figure out how you want to fix them. Delete the rows? Change the date to 1941-12-07, 2003-03-20, or 2022-02-24 (dates that live in infamy)? We can't tell you what you need to do here.

Then, fix them. If there are only one or two, fix them one by one.

  UPDATE mytable
     SET datecol='whatever replacement date'
   WHERE id='the id of the offending row.'

or

  DELETE FROM mytable
        WHERE id='the id of the offending row.'

If there are thousands of them, you could bulk fix them with something like this. *But don't do this without first working through the problem very carefully on a test server. If you make a mistake you will trash your table.*.

  UPDATE mytable
     SET datecol='whatever replacement date'
   WHERE '0000-00-00' = DATE_FORMAT(datecol,'%Y-%m-%d')

After you've finished correcting your problems, go back and do your SELECT *, to make sure you got them all.

Then disable ALLOW_INVALID_DATES and never re-enable it again.

That should clean up the mess. Notice that real-world data always have some rows that aren't perfect in it.