Mysql 日期时间 DATE_FORMAT(date,format)

本文转自：http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format

date_format(date,format)

formats the date value according to the format string.

the following specifiers may be 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.

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 english 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 year (001..366)
%k	hour (0..23)
%l	hour (1..12)
%m	month name (january..december)
%m	month, numeric (00..12)
%p	am or 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 first day of the week
%u	week (00..53), where monday is the first day of the week
%v	week (01..53), where sunday is the first day of the week; used with %x
%v	week (01..53), where monday is the first day of the week; used with %x
%w	weekday name (sunday..saturday)
%w	day of the week (0=sunday..6=saturday)
%x	year for the week where sunday is the first day of the week, numeric, four digits; used with %v
%x	year for the week, where monday is the first day of the week, numeric, four digits; used with %v
%y	year, numeric, four digits
%y	year, numeric (two digits)
%%	a literal “%” character
%x	x, 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 is 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 and collation_connection so that 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 00 thu 04 10 oct 277'
mysql> select 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'
mysql> select date_format('1999-01-01', '%x %v');
    -> '1998 52'
mysql> select date_format('2006-06-00', '%d');
    -> '00'