Outdated Version
You are viewing an older version of this section. View current production version.
DATE_FORMAT
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 |
+------------------------------------------------------------+
