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"