MySQL Cookbook

5.2 Changing MySQL's Date Format

5.2.1 Problem

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

5.2.2 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.

5.2.3 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.^[1] 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.

^[1] Chapters Chapter 6 and Chapter 7 discuss ordering and grouping techniques for date-based values.

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 column. But then you can't operate on them as dates.) In some cases, if your values are close to ISO format, rewriting may not be necessary. For example, the string values 87-1-7 and 1987-1-7 and the numbers 870107 and 19870107 all are interpreted by MySQL as the date 1987-01-07 when loaded into a DATE column. The topic of date rewriting for data entry is covered in Chapter 10.

• For display purposes, you can present dates in non-ISO format by rewriting them. MySQL's DATE_FORMAT( ) function can be helpful here. It provides a lot of flexibility for producing whatever format you want (see Recipe 5.3 and Recipe 5.5). You can also use functions such as YEAR( ) to extract parts of dates (see Recipe 5.6). Additional discussion may be found in Chapter 10, which includes a short script that dumps table contents with the date columns reformatted.