MySQL-DATE_FORMAT函数

常用函数

DATE_FORMAT(date,format)
样例:

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')		 // Nov 04 2008 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y')				// 11-04-2008
DATE_FORMAT(NOW(),'%d %b %y')				// 04 Nov 08
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')			 // 04 Nov 2008 11:45:34:243

format符号

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