MySQL: Function 3: Date and Time Functions: Important formatting Functions

Date_format (Date,Format_ Str)

FormatsDateValue according
TheFormatString.

The following specifiers MAY BE USED INFormatString. The"%"Character is required before format specifier
Characters.

Specifier	Description
%	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	AMOrPM
% R	Time, 12-hour (Hh: mm: SSFollowedAMOrPM)
% 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% X
% V	Week (01..53), Where Monday is the first day of the week; used% 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% V
% X	Year for the week, where Monday is the first day of the week, numeric, four Digits; used% V
% Y	Year, numeric, four digits
% Y	Year, numeric (two digits)
%	A literal"%" Character
%X	X, For any"X"Not listed Above

Ranges for the month and day specifiers begin with zero due to the fact that
MySQL allows the storing of incomplete dates such'2017-00-00'.

As of MySQL 5.1.12, the language used for day and month names and
Abbreviations is controlled by the value ofLc_time_namesSystem variable (section 9.8, "MySQL
Server locale support ").

As of MySQL 5.1.15,Date_format ()Returns a string with a character set and
Collation givenCharacter_set_connectionAndCollation_connectionSo that it can return month and
Weekday names containing non-ASCII characters. Before 5.1.15, the return value
Is a binary string.

Mysql> Select date_format ('2017-10-04 22:23:00 ',' % w % m % y '); 
-> 'Sunday October 2009'
Mysql> Select date_format ('2017-10-04 22:23:00 ',' % H: % I: % s '); 
-> '22: 23: 00'
Mysql> Select date_format ('2017-10-04 22:23:00 ', 
-> '% D % Y % A % d % m % B % J '); 
-> '4th 00 Thu 04 10 Oct 277'
Mysql> Select date_format ('2017-10-04 22:23:00 ', 
-> '% H % K % I % R % T % S % W '); 
-> '22 22 10 10:23:00 PM 22:23:00 6'
Mysql> Select date_format ('2017-01-01 ',' % x % V '); 
-> '2014 52'
Mysql> Select date_format ('2017-06-00 ',' % D '); 
-> '00'

Get_format ({date | time | datetime },
{'Eur' | 'usa' | 'jis '| 'iso' | 'internal '})

Returns a format string. This function is useful in combination withDate_format ()AndStr_to_date ()Functions.

The possible values for the first and second arguments result in several
Possible format strings (for the specifiers used, see the table inDate_format ()Function Description). ISO Format refers
To ISO 9075, not ISO 8601.

Function call	Result
Get_format (date, 'USA ')	'% M. % d. % y'
Get_format (date, 'jis ')	'% Y-% m-% d'
Get_format (date, 'iso ')	'% Y-% m-% d'
Get_format (date, 'eur ')	'% D. % m. % y'
Get_format (date, 'internal ')	'% Y % m % d'
Get_format (datetime, 'USA ')	'% Y-% m-% d % H. % I. % s'
Get_format (datetime, 'jis ')	'% Y-% m-% d % H: % I: % s'
Get_format (datetime, 'iso ')	'% Y-% m-% d % H: % I: % s'
Get_format (datetime, 'eur ')	'% Y-% m-% d % H. % I. % s'
Get_format (datetime, 'internal ')	'% Y % m % d % H % I % s'
Get_format (time, 'USA ')	'% H: % I: % S % P'
Get_format (time, 'jis ')	'% H: % I: % s'
Get_format (time, 'iso ')	'% H: % I: % s'
Get_format (time, 'eur ')	'% H. % I. % s'
Get_format (time, 'internal ')	'% H % I % s'

Date, and
Timestamp types "href =" ch10s03.html # datetime ">TimestampCan also
Be used as the first argumentGet_format (), In which case the function returns
Same values as for date, and
Timestamp types "href =" ch10s03.html # datetime ">Datetime.

Mysql>Select date_format ('2017-10-03 ', get_format (date, 'eur '));
-> '03. 10.2003'
Mysql>Select str_to_date ('10. 31.2003 ', get_format (date, 'USA '));
-> '2017-10-31'