MySQL DATE_FORMAT () function
Definition and usage
The DATE_FORMAT () function is used to display date/time data in different formats.
Syntax
DATE_FORMAT (date, format)
The date parameter is a valid date. Format specifies the output format of the date/time.
The following formats are available:
Format description
% A abbreviated name for a week
% B abbreviated month name
% C month, numeric value
% D the day of the month with an English prefix
Day of % d month, value (00-31)
% E day (0-31)
% F microseconds
% H hour (00-23)
% H hour (01-12)
% I hour (01-12)
% I minutes, numeric value (00-59)
% J-day (001-366)
% K hour (0-23)
% L hour (1-12)
% M month name
% M month, value (00-12)
% P AM or PM
% R time, 12-hour (hh: mm: ss AM or PM)
% S seconds (00-59)
% S seconds (00-59)
% T time, 24-hour (hh: mm: ss)
% U Week (00-53) Sunday is the first day of the week
% U Week (00-53) Monday is the first day of the week
% V Week (01-53) Sunday is the first day of the week, used with % X
% V Week (01-53) Monday is the first day of the week, used with % x
% W week name
% W Day (0 = Sunday, 6 = Saturday)
% X, where Sunday is the first day of the week, 4 digits, used with % V
% X, where Monday is the first day of the week, 4 digits, used with % v
% Y year, 4 digits
% Y years, two places
Instance
The following script uses the DATE_FORMAT () function to display different formats. NOW () is used to obtain the current date/time:
DATE_FORMAT (NOW (), '% B % d % Y % h: % I % p ')
DATE_FORMAT (NOW (), '% m-% d-% y ')
DATE_FORMAT (NOW (), '% d % B % y ')
DATE_FORMAT (NOW (), '% d % B % Y % T: % F ')
The result is similar:
Dec 29 2008 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46. 635
Instance
SELECT DATE_FORMAT ('order creation time', '% U') as week,
Sum ('Total') as money from corder
Where DATE_FORMAT ('order creation time', '% y') = 2012
Group by week
Result:
Week money
13 854314.83
14 5177385.26
15 4870063.87
16 4529686.53
17 3678584.15
This article permanently updates the link address: