Syntax

DATE_FORMAT(date,format)

Description

Formats the date value according to the format string.

The language used for the names is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.

The options that can be used by DATE_FORMAT(), as well as its inverse STR_TO_DATE() and the FROM_UNIXTIME() function, are:

OptionDescription
%DDay with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%HHour with 2 digits between 00-23.
%IHour with 2 digits between 01-12.
%MFull month name in current locale (Variable lc_time_names).
%SSeconds with 2 digits.
%TTime in 24 hour format. Short for '%H:%i:%S'.
%UWeek number (00-53), when first day of the week is Sunday.
%VWeek number (01-53), when first day of the week is Sunday. Used with %X.
%WFull weekday name in current locale (Variable lc_time_names).
%XYear with 4 digits when first day of the week is Sunday. Used with %V.
%YYear with 4 digits.
%aShort weekday name in current locale (Variable lc_time_names).
%bShort form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%cMonth with 1 or 2 digits.
%dDay with 2 digits.
%eDay with 1 or 2 digits.
%fSub seconds 6 digits.
%hHour with 2 digits between 01-12.
%iMinute with 2 digits
%jDay of the year (001-366)
%kHour with 1 digits between 0-23.
%lHour with 1 digits between 1-12.
%mMonth with 2 digits.
%pAM/PM according to current locale (Variable lc_time_names).
%rTime in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%sSeconds with 2 digits.
%uWeek number (00-53), when first day of the week is Monday.
%vWeek number (01-53), when first day of the week is Monday. Used with %x.
%wDay of the week. 0 = Sunday, 1 = Saturday.
%xYear with 4 digits when first day of the week is Sunday. Used with %v.
%yYear with 2 digits.
#For str_to_date(), skip all numbers.
.For str_to_date(), skip all punctation characters.
@For str_to_date(), skip all alpha characters.
%%A literal % character.

To get a date in one of the standard formats, GET_FORMAT() can be used.

Examples

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277                                   |
+------------------------------------------------------------+

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                         |
+------------------------------------------------------------+

SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+

SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00                              |
+---------------------------------+

Comments

Comments loading...