MySQL Cookbook

Changing MySQL’s Date Format

Problem

You want to change the format that MySQL uses for representing date values.

Solution

You can’t. However, you can rewrite input values into the proper format when storing dates, and you can rewrite them into fairly arbitrary format for display by using the DATE_FORMAT( ) function.

Discussion

The CCYY-MM-DD format that MySQL uses for DATE values follows the ISO 8601 standard for representing dates. This format has the useful property that because the year, month, and day parts have a fixed length and appear left to right in date strings, dates sort naturally into the proper temporal order.^[28] However, ISO format is not used by all database systems, which can cause problems if you want to move data between different systems. Moreover, people commonly like to represent dates in other formats such as MM/DD/YY or DD-MM-CCYY. This too can be a source of trouble, due to mismatches between human expectations of what dates should look like and the way MySQL actually represents them.

A frequent question from people who are new to MySQL is, “How do I tell MySQL to store dates in a specific format such as MM/DD/CCYY?” Sorry, you can’t. MySQL always stores dates in ISO format, a fact that has implications both for data entry and for result set display:

• For data entry purposes, to store values that are not in ISO format, you normally must rewrite them first. (If you don’t want to rewrite your dates, you’ll need to store them as strings, for example, in a CHAR ...