MariaDB date format specifiers
In MariaDB, the DATE_FORMAT() function is used to format datetime values, which require format specifiers to specify the format to output.
Below are all the format specifiers supported by MariaDB, which you can combine to apply to the above functions.
| Format specifiers | Description |
|---|---|
%a |
The short day name in the current locale. |
%b |
The short month name in the current locale. For example Feb in an English environment. |
%c |
Use 1 or 2 digits for the month. |
%D |
Days with English suffixes (th, nd, st, rd) (e.g. 1st, 2nd, 3rd…) |
%d |
Use 2 digits for the day. |
%e |
Use 1 or 2 digits for the day. |
%f |
Use 6 digits for fractional seconds. |
%H |
Use 2 digits for the hour (00-23). |
%h |
Use 2 digits for the hour (01-12). |
%I |
Use 2 digits for the hour (01-12). |
%i |
Use 2 digits for the minute (00-59). |
%j |
Day of the year (001-366) |
%k |
hour (0-23) |
%l |
hours (1-12) |
%M |
The full month name in the current locale. |
%m |
Month, 2 digits (01-12). |
%p |
AM/PM according to the current locale. |
%r |
The time is in 12-hour format followed by AM/PM. Short for %I:%i:%S %p. |
%S |
Use 2-digit for seconds (00-59). |
%s |
Use 2-digit for seconds (00-59). |
%T |
The time in 24-hour format. Short for %H:%i:%S. |
%U |
Week number (00-53), Sunday is the first day of the week. |
%u |
Week number (00-53), Monday is the first day of the week. |
%V |
Week number (01-53), Sunday is the first day of the week, used with %X. |
%v |
Week number (01-53), Monday is the first day of the week, used with %x. |
%W |
The full day name in the current locale. |
%w |
Day of the week. 0 = Sunday and 6 = Saturday. |
%X |
Year, Sunday is the first day of the week, 4 digits, used with %V. |
%x |
Year, Monday is the first day of the week, 4 digits, used with %v. |
%Y |
Year, 4 digits. |
%y |
Year, 2 digits. |
%# |
For STR_TO_DATE(), skip all numbers. |
%. |
For STR_TO_DATE(), skip all punctuation characters. |
%@ |
For STR_TO_DATE(), all alphabetic characters are skipped. |
%% |
% character. |
You can combine these format specifiers and used them in the DATE_FORMAT() function, as follows:
SELECT DATE_FORMAT('2022-01-02 03:04:15', '%Y/%m/%d %r %W');
Output:
+------------------------------------------------------+
| DATE_FORMAT('2022-01-02 03:04:15', '%Y/%m/%d %r %W') |
+------------------------------------------------------+
| 2022/01/02 03:04:15 AM Sunday |
+------------------------------------------------------+Likewise, format specifiers are available for conversion functions such as STR_TO_DATE() and FROM_UNIXTIME().