MYSQL Date_format (Data,format)

Date_format (Date,format)

Formats the date value according to the format string.

The following specifiers is used in the format string. As of MySQL 3.23, the "%" character is required before format specifier characters. In earlier versions of MySQL, "%" was optional.

SpecifierDescription

%aabbreviated weekday name (Sun.. Sat)

%babbreviated month name (Jan: DEC)

%cMonth, numeric (0..12)

%dDay of the month with 中文版 suffix (0th, 1st, 2nd, 3rd, ...)

%dDay of the month, numeric (00..31)

%eDay of the month, numeric (0..31)

%fmicroseconds (000000..999999)

%HHour (00..23)

%hHour (01..12)

%IHour (01..12)

%iMinutes, numeric (00..59)

%jDay of the Year (001..366)

%kHour (0..23)

%lHour (1..12)

%MMonth name (January). December)

%mMonth, numeric (00..12)

%pAM or PM

%rtime, 12-hour (Hh:mm:ss followed by AM or PM)

%sSeconds (00..59)

%sSeconds (00..59)

%Ttime, 24-hour (HH:MM:SS)

%uWeek (00..53), where Sunday is the first day of the Week

%uWeek (00..53), where Monday is the first day of the Week

%VWeek (01..53), where Sunday is the first day of the Week; Used with%x

%vWeek (01..53), where Monday is the first day of the Week; Used with%x

%WWeekday name (Sunday. Saturday)

%wDay of the week (0=sunday. 6=saturday)

%xYear for the week where Sunday are the first day of the week, numeric, and four digits; Used with%V

%xYear for the week, where Monday are the first day of the week, numeric, and four digits; Used with%v

%Y, numeric, four digits

%yYear, numeric (digits)

PercentA literal "%" character

%xx, for any ' x ' not listed above

The%v,%v,%x, and%x format specifiers are available as of MySQL 3.23.8. %f is available as of MySQL 4.1.1.

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as ' 2014-00-00 ' (as of MySQL 3.23).

As of MySQL 4.1.21, the language used for day and month names and abbreviations are controlled by the value of the Lc_time_ Names system variable (section 9.8, "MySQL Server Locale support").

As of MySQL 4.1.23, Date_format () returns a string with a character set and collation given by character_set_connection an D collation_connection so it can return month and weekday names containing non-ascii characters. Before 4.1.23, the return value is a binary string.

mysql> SELECT date_format (' 2009-10-04 22:23:00 ', '%W%M%Y ');

' Sunday October 2009 '

mysql> SELECT date_format (' 2007-10-04 22:23:00 ', '%h:%i:%s ');

' 22:23:00 '

mysql> SELECT date_format (' 1900-10-04-22:23:00 ',

, '%d%y%a%d%m%b%j ');

, ' 4th Thu Oct 277 '

mysql> SELECT date_format (' 1997-10-04-22:23:00 ',

'%H%k%I%r%T%s%w ');

-10:23:00 PM 22:23:00 00 6 '

mysql> SELECT date_format (' 1999-01-01 ', '%x%V ');

' 1998 52 '

mysql> SELECT date_format (' 2006-06-00 ', '%d ');

' 00 '

MYSQL Date_format (Data,format)