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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.