DATE_FORMAT
Syntax
DATE_FORMAT(date, format[, locale])Description
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
Microseconds 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.
%Z
Timezone abbreviation. From MariaDB 11.3.0.
%z
Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From MariaDB 11.3.0.
%#
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.
Examples
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') |
+------------------------------------------+
| Κυριακή |
+------------------------------------------+From MariaDB 11.3, the timezone information:
SELECT DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z');
+--------------------------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z') |
+--------------------------------------------------+
| Wednesday 20 September 2023 15:00:23 SAST +0200 |
+--------------------------------------------------+See Also
This page is licensed: GPLv2, originally from fill_help_tables.sql
Last updated
Was this helpful?