Working with Dates and Times - MySQL - page 185

The DATE_FORMAT() function takes two arguments: a DATE , DATETIME , or TIMESTAMP
value, and a string describing how to display the value. The format string uses the same
kind of specifiers as STR_TO_DATE() . The following statement 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 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
| 2017-03-16 | March 16, 2017 |
+------------+----------------------------+
Because DATE_FORMAT() produces long column headings, it's often useful to provide an
alias (see Recipe 3.2 ) 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 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
| 2017-03-16 | March 16, 2017 |
+------------+-------------------+
The MySQL Reference Manual provides a complete list of format sequences to use with
DATE_FORMAT() , TIME_FORMAT() , and STR_TO_DATE() . The following table shows some
of them:

Sequence Meaning

Four-digit year

%Y

Two-digit year

%y

Complete month name

%M

Month name, initial three letters

%b

Two-digit month of year (01..12)

%m

Month of year (1..12)

%c

Two-digit day of month (01..31)

%d

Day of month (1..31)

%e

Weekday name (Sunday..Saturday)

%W

12-hour time with AM or PM suffix

%r

24-hour time

%T