MySQL Cookbook

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