Mysql Date Time Date_format (Date,format) _mysql

This article turns from: Http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format

DATE_FORMAT(date,format)

Formats the format value according to the string. date

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

specifier	Description
%a	Abbreviated weekday name ( Sun ... Sat )
%b	Abbreviated month name ( Jan ... Dec )
%c	Month, numeric ( 0 ... 12 )
%D	Day of the month with 中文版 suffix ( 0th ,,, 1st 2nd 3rd , ...)
%d	Day of the month, numeric ( 00 ... 31 )
%e	Day of the month, numeric ( 0 ... 31 )
%f	Microseconds ( 000000 ... 999999 )
%H	Hour ( 00 ... 23 )
%h	Hour ( 01 ... 12 )
%I	Hour ( 01 ... 12 )
%i	Minutes, numeric ( 00 ... 59 )
%j	Day of the year ( 001 .. 366 )
%k	Hour ( 0 ... 23 )
%l	Hour ( 1 ... 12 )
%M	Month name ( January .. December )
%m	Month, numeric ( 00 ... 12 )
%p	AMor PM
%r	Time, 12-hour ( hh:mm:ss followed by AM or PM )
%S	Seconds ( 00 ... 59 )
%s	Seconds ( 00 ... 59 )
%T	Time, 24-hour ( hh:mm:ss )
%U	Week ( 00 .. 53 ), where Sunday is the the the the the the The Week
%u	Week ( 00 .. 53 ), where Monday is the the the the the the The Week
%V	Week ( 01 . 53 ), where Sunday is the the "the" the "the", the Week; used %X with
%v	Week ( 01 . 53 ), where Monday is the the "the" the "the", the Week; used %x with
%W	Weekday name ( Sunday .. Saturday )
%w	Day of the week ( 0 =sunday. 6 =saturday)
%X	Year for the week where Sunday is the the week, numeric, four digits; %Vused with
%x	Year for the week, where Monday is the the "the", the the week, numeric, four digits; %vused with
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal " % " character
%x	x, for no " x " not listed above

The %v , %V , %x , and %X format specifiers are available as of MySQL 3.23.8. is available as of %f MySQL 4.1 .1.

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

As of the MySQL 4.1.21, the language used for day and month names and abbreviations are controlled by the value of the lc_time_names Ystem 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 give N by character_set_connection and collation_connection so That's 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 6 ' mysql>   SELECT date_format (' 1999-01-01 ', '%x %V ');  
    -> ' 1998 ' mysql>   SELECT date_format (' 2006-06-00 ', '%d ');   -> '