MySQL Cookbook

5.3 Telling MySQL How to Display Dates or Times

5.3.1 Problem

You want to display dates or times in a format other than what MySQL uses by default.

5.3.2 Solution

Use the DATE_FORMAT( ) or TIME_FORMAT( ) functions to rewrite them.

5.3.3 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 date_val;
+------------+-------------------+
| d          | date              |
+------------+-------------------+
| 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     |
+------------+-------------------+

The MySQL Reference Manual provides a complete list of format sequences. Some of the more common ones are shown in the following table:

Sequence	Meaning
%Y	Four-digit year
%y	Two-digit year
%M	Complete month name
%b	Month name, initial three letters
%m	Two-digit month of year (01..12)
%c	Month of year (1..12)
%d	Two-digit day of month (01..31)
%e	Day of month (1..31)
%r	12-hour time with AM or PM suffix
%T	24-hour time
%H	Two-digit hour
%i	Two-digit minute
%s	Two-digit second
%%	Literal %

The time-related format sequences shown in the table are useful only when you pass DATE_FORMAT( ) a value that has both date and time parts (a DATETIME or TIMESTAMP). The following query demonstrates how to display DATETIME values from the datetime_val table using formats that include the time of day:

mysql> SELECT dt,
    -> DATE_FORMAT(dt,'%c/%e/%y %r') AS format1,
    -> DATE_FORMAT(dt,'%M %e, %Y %T') AS format2
    -> FROM datetime_val;
+---------------------+----------------------+----------------------------+
| dt                  | format1              | format2                    |
+---------------------+----------------------+----------------------------+
| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM   | January 1, 1970 00:00:00   |
| 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM   | March 5, 1987 12:30:15     |
| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM   | June 4, 2000 15:45:30      |
+---------------------+----------------------+----------------------------+

TIME_FORMAT( ) is similar to DATE_FORMAT( ), but understands only time-related specifiers in the format string. TIME_FORMAT( ) works with TIME, DATETIME, or TIMESTAMP values.

mysql> SELECT dt,
    -> TIME_FORMAT(dt, '%r') AS '12-hour time',
    -> TIME_FORMAT(dt, '%T') AS '24-hour time'
    -> FROM datetime_val;
+---------------------+--------------+--------------+
| dt                  | 12-hour time | 24-hour time |
+---------------------+--------------+--------------+
| 1970-01-01 00:00:00 | 12:00:00 AM  | 00:00:00     |
| 1987-03-05 12:30:15 | 12:30:15 PM  | 12:30:15     |
| 1999-12-31 09:00:00 | 09:00:00 AM  | 09:00:00     |
| 2000-06-04 15:45:30 | 03:45:30 PM  | 15:45:30     |
+---------------------+--------------+--------------+