DATE_
On this page
Convert the input datetime object to a string according to the specified format.
Syntax
DATE_FORMAT (dateobj, format)Arguments
-
dateobj: a valid date, datetime, or parsable date string
-
format: a string containing format symbols
Year arguments
|
Symbol |
Output |
|---|---|
|
%Y |
Year, numeric, four digits |
|
%y |
Year, numeric (two digits) |
|
%j |
Day of year (001 to 366) |
Month arguments
|
Symbol |
Output |
|---|---|
|
%b |
Abbreviated month (Jan, Feb, Mar…) |
|
%M |
Month name (January to December) |
|
%m |
Month number, padded (00 to 12) |
|
%c |
Month number (0 to 12) |
Day arguments
|
Symbol |
Output |
|---|---|
|
%D |
Day of the month with suffix (0th, 1st, 2nd, etc) |
|
%d |
Day of the month, padded (00 to 31) |
|
%e |
Day of the month (0 to 31) |
Hour arguments
|
Name |
Description |
|---|---|
|
%H |
Hour of day, padded 24h format (00 to 23) |
|
%h |
Hour of day, padded 12h format (01 to 12) |
|
%I |
(alias for %h) |
|
%k |
Hour of day, 24h format (0 to 23) |
|
%l |
Hour of day, 12h format (1 to 12) |
Minute arguments
|
Symbol |
Output |
|---|---|
|
%i |
Minute of hour (00 to 59) |
Second arguments
|
Symbol |
Output |
|---|---|
|
%S |
Seconds (00 to 59) |
|
%s |
Seconds (00 to 59) |
Microsecond arguments
|
Symbol |
Output |
|---|---|
|
%f |
Microseconds (000000 to 999999) |
Time arguments
|
Symbol |
Output |
|---|---|
|
%p |
AM or PM |
|
%r |
Time, 12-hour (hh:mm:ss followed by AM or PM) |
|
%T |
Time, 24-hour (hh:mm:ss) |
Week arguments
|
Symbol |
Output |
|---|---|
|
%U |
Week (00 to 53) starting Sunday |
|
%u |
Week (00 to 53) starting Monday |
|
%V |
Week (01 to 53) starting Sunday |
|
%v |
Week (01 to 53) starting Monday |
|
%X |
Year corresponding to week given by %V |
|
%x |
Year corresponding to week given by %v |
Weekday arguments
|
Symbol |
Output |
|---|---|
|
%W |
Weekday name (Sunday to Saturday) |
|
%w |
Day of the week (0=Sunday to 6=Saturday) |
|
%a |
Abbreviated day of week (Sun, Mon, Tue, etc) |
Escape arguments
|
Symbol |
Output |
|---|---|
|
%% |
A literal % character |
Return Type
String
Examples
SELECT DATE_FORMAT(NOW(), '%M %D, %Y');
+---------------------------------+
| DATE_FORMAT(NOW(), '%M %D, %Y') |
+---------------------------------+
| March 3rd, 2015 |
+---------------------------------+SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s');
+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s') |
+-----------------------------------------+
| 2015-03-03 11:37:07 |
+-----------------------------------------+SELECT DATE_FORMAT(NOW(), '%W, the %D day of %M in that remarkable year %Y') AS florid;
+------------------------------------------------------------+
| florid |
+------------------------------------------------------------+
| Tuesday, the 3rd day of March in that remarkable year 2015 |
+------------------------------------------------------------+Last modified: May 31, 2023