Telling MySQL How to Display Dates or Times
Problem
You want to display dates or times in a format other than what MySQL uses by default.
Solution
Use the DATE_FORMAT( )
or TIME_FORMAT( ) functions to rewrite them.
Discussion
As already noted, MySQL displays dates in ISO format unless you tell
it otherwise. To rewrite date values into other formats, use the
DATE_FORMAT( ) function, which takes two
arguments: a DATE, DATETIME, or
TIMESTAMP value, and a string describing how to
display the value. Within the formatting string, you indicate what to
display using special sequences of the form
%c, where
c specifies which part of the date to
display. For example, %Y, %M,
and %d signify the four-digit year, the month
name, and the two-digit day of the month. The following query shows
the values in the date_val table, both as MySQL
displays them by default and as reformatted with
DATE_FORMAT( ):
mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val;
+------------+----------------------------+
| d | DATE_FORMAT(d,'%M %d, %Y') |
+------------+----------------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900 |
| 1987-03-05 | March 05, 1987 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
+------------+----------------------------+Clearly, DATE_FORMAT( ) tends to produce rather
long column headings, so it’s often useful to
provide an alias to make a heading more concise or meaningful:
mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM ...