DATE_FORMAT
You are viewing an old version of this article. View
the current version here.
Syntax
DATE_FORMAT(date,format)
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 |
|---|---|
%D | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...). |
%H | Hour with 2 digits between 00-23. |
%I | Hour with 2 digits between 01-12. |
%M | Full month name in current locale (Variable lc_time_names). |
%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. |
%V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
%W | Full weekday name in current locale (Variable lc_time_names). |
%X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
%Y | Year with 4 digits. |
%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 2 digits. |
%e | Day with 1 or 2 digits. |
%f | Sub seconds 6 digits. |
%h | 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 | 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. |
%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 Monday. Used with %x. |
%w | Day of the week. 0 = Sunday, 1 = Saturday. |
%x | Year with 4 digits when first day of the week is Sunday. Used with %v. |
%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.
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 | +---------------------------------+
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
