You may have to set the datetime_format in my.cnf
Here is what is very, very unusual. The variables date_format and datetime_format are present in MySQL 5.x:
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.0.81-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)
If you look them up in the MySQL Documentation, it says they are unused. Yet, to my surprise, you can set them in your session. Here is how:
mysql> set date_format = '%Y%m%d';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| date_format | %Y%m%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)
Question: Can you set it globally ??? (This example is MySQL 5.5.12 in Windows)
mysql> set global date_format = '%Y%m%d';
ERROR 1238 (HY000): Variable 'date_format' is a read only variable
At least you cannot on the command line. What about in my.cnf (my.ini) ???
I added this to my.ini on my PC
[mysqld]
date_format="%Y%m%d"
restarted mysql and got this:
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.12-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| date_format | %Y%m%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)
Although it is officially undocumented, you can play with the format at your discretion.
Give it a Try !!!
DISCLAIMER : Should any of your datetime data be caught or killed, I will disavow any knowledge of your actions.