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