MySQL DATE_FORMAT () function, mysqldate_format

MySQL DATE_FORMAT () function, mysqldate_format
Definition and usage

The DATE_FORMAT () function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)

DateThe parameter is a valid date.FormatSpecifies the output format of the date/time.

The following formats are available:

Format	Description
%	Abbreviated name of a week
% B	Abbreviated month name
% C	Month, Value
% D	Day of the month with an English prefix
% D	Day of the month, numeric value (00-31)
% E	Day of the month, value (0-31)
% F	Microseconds
% H	Hour (00-23)
% H	Hour (01-12)
% I	Hour (01-12)
% I	Minute, value (00-59)
% J	Day of the year (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 of the week (0 = Sunday, 6 = Saturday)
% X	Year, where Sunday is the first day of the week, four digits, used with % V
% X	Year, where Monday is the first day of the week, 4 digits, used with % v
% Y	Year, 4 digits
% Y	Year, 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 11:45 PM12-29-200829 Dec 0829 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 GROUPWeek result: week money 13854314.83 145177385.26 154870063.87 164529686.53 173678584.15