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