The following table contains a full list of format specifiers that can be used in format strings with functions like DATE_FORMAT(), STR_TO_DATE(), and FROM_UNIXTIME() in MySQL.
| Specifier | Description |
|---|---|
%a | 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 | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%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; WEEK() mode 0 |
%u | Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
%V | Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
%v | Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %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 with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
% | x, for any “x” not listed above |
Source: MySQL documentation
Example
The above format specifiers can be used to construct a format string as required. Here’s an example of using some of the above format specifiers to provide a format string for the DATE_FORMAT() function:
SELECT DATE_FORMAT('2029-10-16 08:09:17', '%r %W, %D %M %Y');Result:
+-------------------------------------------------------+
| DATE_FORMAT('2029-10-16 08:09:17', '%r %W, %D %M %Y') |
+-------------------------------------------------------+
| 08:09:17 AM Tuesday, 16th October 2029 |
+-------------------------------------------------------+
Here’s an example of a format string consisting of just one format specifier:
SELECT DATE_FORMAT('2029-10-16', '%W');Result:
+---------------------------------+
| DATE_FORMAT('2029-10-16', '%W') |
+---------------------------------+
| Tuesday |
+---------------------------------+
The GET_FORMAT() Function
In addition to the above individual format specifiers, you can use the GET_FORMAT() function to return the full format string, without needing to remember which format specifiers, and in which order, to include in the format string. This function can be useful for when working with functions that require a format string.