MySQL in a Nutshell, 2nd Edition

Name

DATE_FORMAT()

Synopsis

DATE_FORMAT(date, 'format_code')

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