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 ...
Get MySQL in a Nutshell, 2nd Edition 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.