We recently encountered an interesting problem with the way DATE and DATETIME fields are handled by MySQL. An application we were developing complained that the following dates were invalid

when querying a MySQL database:

  0000-00-00 00:00:00

This is of course a nonsensical date, so it is not difficult to understand the objection - but where were these values coming from?

It turns out that these values are unique to MySQL and have special meaning - they are known as 'zero' dates and MySQL inserts them (by defauilt) whenever an invalid date value is presented by a client (raising a warning in the process).

We can see this in action - try the following statements which insert an non-existent date (31st April) into a MySQL table:

  mysql> CREATE TABLE test_table (test_date DATETIME);

  mysql> INSERT INTO test_table VALUES ('2000-04-31 00:00:00');

  mysql> SHOW WARNINGS;

  +---------+------+--------------------------------+
  | Level   | Code | Message                        |
  +---------+------+--------------------------------+
  | Warning | 1264 | Out of range value adjusted ...|
  +---------+------+--------------------------------+ 

When checking the field we find the 'zero' date inserted by MySQL, rather than the invalid date specified:

  mysql> SELECT test_date FROM test_table;

  +---------------------+
  | test_date           |
  +---------------------+
  | 0000-00-00 00:00:00 |
  +---------------------+ 

However, further investigation on other DATETIME fields showed that some 'invalid' dates did actually exist (in the database (rather than the 'zero' date noted above). How can this have happened if MySQL automatically converts all invalid dates to the 'zero' date?

This caused some head-scratching until it was revealed that the MySQL server had been upgraded from version 4.x to 5.x, which explains the dilemma.

In MySQL 5.0.2 handling of 'invalid' dates was changed with the 'zero' date being inserted whenever invalid dates were encountered. Prior to version 5.0.2 invalid dates were allowed (by default) as per the current ALLOW_INVALID_DATES mode.

The default date-handling behaviour can be changed within the MySQL server through the application of 'SQL Modes'. These are essentially a set of variables that change MySQL handling of variables at either a 'global' or 'session' level.

We can check the current 'modes' as follows:

  SELECT @@global.sql_mode;

  +-------------------+
  | @@global.sql_mode |
  +-------------------+
  |                   |
  +-------------------+ 

This will return a single text field with comma-delimited values - each value defines a particular 'mode' for the MySQL server. In our instance we were presented with an empty field, impliing that no modes had been set.

There are several 'modes' that can affect how MySQL handles invalid DATE and DATETIME fields, including NO_ZERO_DATE, STRICT_ALL_TABLES and STRICT_TRANS_TABLES.

However, the main one of relevance for us was:

  ALLOW_INVALID_DATES

Enable this mode and see what happens:

  mysql> SET @@session.sql_mode = 'ALLOW_INVALID_DATES';

Note the '@@session' above, which changes for mode for our current session only - in other words the mode is only effective for our connection to the MySQL server - other users will not be affected. You can change the mode globally at runtime as follows, which will affect all users:

  mysql> SET @@global.sql_mode = 'ALLOW_INVALID_DATES';

We can also set this 'mode' globally via the 'my.cnf' configuration file, which will require a restart of the MySQL server to take effect:

  [mysqld]
  sql_mode="ALLOW_INVALID_DATES"

Once the ALLOW_INVALID_DATES mode has been set we try to insert an invalid date again - we now see that MySQL no longer raises a warning and does not use the 'zero' date. Instead it allows the incorrect value to be inserted into the table:

  mysql> INSERT INTO test_table VALUES ('2000-04-31 00:00:00');

  mysql> SELECT test_date FROM test_table;

  +---------------------+
  | test_date           |
  +---------------------+
  | 0000-00-00 00:00:00 |
  | 2000-04-31 00:00:00 |
  +---------------------+ 

The logic behind all this date-handling might all seem a little odd.

However, the reason put forward by the MySQL team is that, in general, the actual values stored in DATE and DATETIME fields are the responsibility of the user, not the database - it should be the application that ensures a date is valid before inserting it into the database.

For example, an application may want to log invalid date usage.