The DATE_FORMAT function converts the value of strings with DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) to specified date/time format and then return the value with the VARCHAR data type.
DATE_FORMAT(date, format)
Default Date/Time Format
Date/Time Type |
Default Output Format |
---|---|
DATE |
'MM/DD/YYYY' |
TIME |
'HH:MI:SS AM' |
TIMESTAMP |
'HH:MI:SS AM MM/DD/YYYY' |
DATETIME |
'HH:MI:SS.FF AM MM/DD/YYYY |
format Value |
Meaning |
---|---|
%a |
Weekday, English abbreviation (Sun, …, Sat) |
%b |
Month, English abbreviation (Jan, …, Dec) |
%c |
Month (1, …, 12) |
%D |
Day of the month, English ordinal number (1st, 2nd, 3rd, ...) |
%d |
Day of the month, two-digit number (01, …, 31) |
%e |
Day of the month (1, …, 31) |
%f |
Microseconds, three-digit number (000, …, 999) |
%H |
Hour, 24-hour based, number with at least two--digit (00, …, 23, …, 100, …) |
%h |
Hour, 12-hour based two-digit number (01, …, 12) |
%I |
Hour, 12-hour based two-digit number (01, …, 12) |
%i |
Minutes, two-digit number (00, …, 59) |
%j |
Day of year, three-digit number (001, …, 366) |
%k |
Hour, 24-hour based, number with at least one-digit (0, …, 23, …, 100, …) |
%l |
Hour, 12-hour based (1, …, 12) |
%M |
Month, English string (January, …, December) |
%m |
Month, two-digit number (01, …, 12) |
%p |
AM or PM |
%r |
Time, 12-hour based, hour:minute:second (hh:mm:ss AM or hh:mm:ss PM) |
%S |
Seconds, two-digit number (00, …, 59) |
%s |
Seconds, two-digit number (00, …, 59) |
%T |
Time, 24-hour based, hour:minute:second (hh:mm:ss) |
%U |
Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53) |
%u |
Week, two-digit number, week number of the year with Monday being the first day (00, …, 53) |
%V |
Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53) |
%v |
Week, two-digit number, week number of the year with Monday being the first day (00, …, 53) |
%W |
Weekday, English string (Sunday, …, Saturday) |
%w |
Day of the week, number index (0=Sunday, …, 6=Saturday) |
%X |
Year, four-digit number calculated as the week number with Sunday being the first day of the week (0000, …, 9999) |
%x |
Year, four-digit number calculated as the week number with Monday being the first day of the week (0000, …, 9999) |
%Y |
Year, four-digit number (0001, …, 9999) |
%y |
Year, two-digit number (00, 01, …, 99) |
%% |
Output the special character "%" as a string |
%x |
Output an arbitrary character x as a string out of English letters that are not used as format specifiers. |
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
date_format('2009-10-04 22:23:00', '%W %M %Y')
======================
'Sunday October 2009'
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
date_format('2007-10-04 22:23:00', '%H:%i:%s')
======================
'22:23:00'
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
======================
'4th 00 Thu 04 10 Oct 277'
SELECT DATE_FORMAT('1999-01-01', '%X %V');
date_format('1999-01-01', '%X %V')
======================
'1998 52'