Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns a date and time in a desired format, based on formatting codes listed within quotes for the second argument of the function. Here is an example:

SELECT DATE_FORMAT(appointment, '%W - %M %e, %Y at %r')
AS 'Appointment'
FROM appointments
WHERE client_id = '8392'
AND appointment > CURDATE( );

+---------------------------------------+
| Appointment                           |
+---------------------------------------+
| Monday - June 16, 2008 at 01:00:00 PM | 
+---------------------------------------+

Using the formatting codes, we’re specifying in this example that we want the name of the day of the week (%W) followed by a dash and then the date of the appointment in a typical U.S. format (%M %e, %Y), with the month name and a comma after the day. We’re ending with the word “at” followed by the full nonmilitary time (%r). The results are returned as a binary string.

As of MySQL version 5.1.15, a string is returned along with the character set and collation of the string, taken from the character_set_connection and the collation_connection system variables. This allows the function to return non-ASCII characters. Here is an example of this function:

SELECT NOW( ), 
DATE_FORMAT(NOW( ), '%M') AS 'Month in Hebrew';

+---------------------+-----------------+
| Now                 | Month in Hebrew |
+---------------------+-----------------+
| 2008-03-14 12:00:24 | מרץ                      | 
+---------------------+-----------------+

In this example, of course, the client and server were set to display Hebrew characters. Also, the server variable lc_time_names was set to Hebrew (he_IL) so as to return the Hebrew word for March. See MySQL’s documentation page on MySQL Server Locale Support (http://dev.mysql.com/doc/refman/5.1/en/locale-support.html) for a list of locale values available for time names.

Table 12-2 contains a list of all the formatting codes you can use with DATE_FORMAT(). You can also use these codes with TIME_FORMAT() and EXTRACT().