Define and use the DATE_FORMAT () function to display date and time data in different formats. Syntax: DATE_FORMAT (date, format) date is a valid date. Format specifies the output format of the date and time. The following statements can be used: & definitions 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 16:25:46 PM 12-29-2008 29 Dec 08 29 Dec 2008. 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