DATE_FORMAT(date, format[, locale])
Formats the date value according to the format string.
The language used for the names is controlled by the value of the lc_time_names system variable. See server locale for more on the supported locales.
The options that can be used by DATE_FORMAT(), as well as its inverse STR_TO_DATE() and the FROM_UNIXTIME() function, are:
| Option | Description |
|---|---|
%a |
Short weekday name in current locale (Variable lc_time_names). |
%b |
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec. |
%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. |
To get a date in one of the standard formats, GET_FORMAT() can be used.
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52 |
+------------------------------------+
SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00 |
+---------------------------------+
Optionally, the locale can be explicitly specified as the third DATE_FORMAT() argument. Doing so makes the function independent from the session settings, and the three argument version of DATE_FORMAT() can be used in virtual indexed and persistent generated-columns:
SELECT DATE_FORMAT('2006-01-01', '%W', 'el_GR');
+------------------------------------------+
| DATE_FORMAT('2006-01-01', '%W', 'el_GR') |
+------------------------------------------+
| Κυριακή |
+------------------------------------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/date_format/