Re: does NO_ZERO_ON_DATE working bad in 5.1?

[Rick James]

Here's a possible clue:

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2004-01-01 22:00:00                                 |
+-----------------------------------------------------+
mysql> SELECT CONVERT_TZ('2004-01-00 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-00 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
mysql> SELECT '2004-01-00' + INTERVAL 0 DAY;
+-------------------------------+
| '2004-01-00' + INTERVAL 0 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> SELECT '20061001' + INTERVAL 0 DAY;
+-----------------------------+
| '20061001' + INTERVAL 0 DAY |
+-----------------------------+
| 2006-10-01                  |
+-----------------------------+

In other words, use some function that would not modify the data except to turn it into NULL if appropriate. Try this:
SELECT data + INTERVAL 0 DAY, data FROM MyTable;