Below is 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 MariaDB.
| Option | Description |
|---|---|
%a | Short weekday name in current locale (Variable lc_time_names). |
%b | Short form month name in current locale. For example, Feb (when using en_US). |
%c | Month with 1 or 2 digits. |
%D | Day with English suffix th, nd, st or rd. (1st, 2nd, 3rd…). |
%d | Day with 2 digits. |
%e | Day with 1 or 2 digits. |
%f | Sub seconds 6 digits. |
%H | Hour with 2 digits between 00-23. |
%h | Hour with 2 digits between 01-12. |
%I | Hour with 2 digits between 01-12. |
%i | Minute with 2 digits. |
%j | Day of the year (001-366) |
%k | Hour with 1 digits between 0-23. |
%l | Hour with 1 digits between 1-12. |
%M | Full month name in current locale (Variable lc_time_names). |
%m | Month with 2 digits. |
%p | AM/PM according to current locale (Variable lc_time_names). |
%r | Time in 12 hour format, followed by AM/PM. Short for %I:%i:%S %p. |
%S | Seconds with 2 digits. |
%s | Seconds with 2 digits. |
%T | Time in 24 hour format. Short for %H:%i:%S. |
%U | Week number (00-53), when first day of the week is Sunday. |
%u | Week number (00-53), when first day of the week is Monday. |
%V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
%v | Week number (01-53), when first day of the week is Monday. Used with %x. |
%W | Full weekday name in current locale (Variable lc_time_names). |
%w | Day of the week. 0 = Sunday, 6 = Saturday. |
%X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
%x | Year with 4 digits when first day of the week is Monday. Used with %v. |
%Y | Year with 4 digits. |
%y | Year with 2 digits. |
%# | For STR_TO_DATE(), skip all numbers. |
%. | For STR_TO_DATE(), skip all punctation characters. |
%@ | For STR_TO_DATE(),skip all alpha characters. |
%% | A literal % character. |
Example
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('2030-01-25 10:30:45', '%r %W, %D %M %Y');Result:
+-------------------------------------------------------+
| DATE_FORMAT('2030-01-25 10:30:45', '%r %W, %D %M %Y') |
+-------------------------------------------------------+
| 10:30:45 AM Friday, 25th January 2030 |
+-------------------------------------------------------+
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.