MySQL Cookbook, 2nd Edition

Changing MySQL’s Date Format

Problem

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

Solution

You can’t. However, you can rewrite non-ISO input values into ISO format when storing dates, and you can rewrite ISO values to other formats 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. Because the year, month, and day parts have a fixed length and appear left to right in date strings, this format has the useful property that dates sort naturally into the proper temporal order. Chapters 7 and 8 discuss ordering and grouping techniques for date-based values.

ISO format, although common, 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 that MySQL actually represents them.

A question frequently asked by people who are new to MySQL is, “How do I tell MySQL to store dates in a specific format such as MM/DD/CCYY?” That’s the wrong question. The right question is, “If I have a date in a specific format, how can I store it in MySQL’s supported format, and vice versa?” MySQL always stores dates in ISO format, a fact that has implications both for data entry and for processing ...