DATE_FORMAT( )

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

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

DATE_FORMAT(date, 'format_code')

Explanation

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.

Examples

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 non-military 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. 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().

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.

  • %%
  • A literal `%'

  • %a
  • Abbreviated weekday name (Sun...Sat)

  • %b
  • Abbreviated month name (Jan...Dec)

  • %c
  • Month, numeric

    (1...12)

  • %d
  • Day of the month, numeric (00...31)

  • %D
  • Day of the month with English suffix (1st, 2nd, 3rd, etc.)

  • %e
  • Day of the month, numeric (0...31)

  • %f
  • Microseconds, numeric (000000...999999)

  • %h
  • Hour (01...12)

  • %H
  • Hour (00...23)

  • %i
  • Minutes, numeric (00...59)

  • %I
  • Hour (01...12)

  • %j
  • Day of the year (001...366)

  • %k
  • Hour (0...23)

  • %l
  • Hour (1...12)

  • %m
  • Month, numeric (01...12)

  • %M
  • Month name (January...December)

  • %p
  • AM or PM

  • %r
  • Time, 12-hour (hh:mm:ss [AM|PM])

  • %s
  • Seconds (00...59)

  • %S
  • Seconds (00...59)

  • %T
  • Time, 24-hour (hh:mm:ss)

  • %u
  • Week, where Monday is the first day of the week (0...52)

  • %U
  • Week, where Sunday is the first day of the week (0...52)

  • %v
  • Week, where Monday is the first day of the week; used with `%x' (1...53)

  • %V
  • Week, where Sunday is the first day of the week; used with `%X' (1...53)

  • %w
  • Day of the week (0=Sunday...6=Saturday)

  • %W
  • Weekday name (Sunday...Saturday)

  • %x
  • Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with `%v' (yyyy)

  • %X
  • Year for the week, where Sunday is the first day of the week, numeric, 4 digits; used with `%V' (yyyy)

  • %y
  • Year, numeric, 2 digits (yy)

  • %Y
  • Year, numeric, 4 digits (yyyy)