DATE_FORMAT
Formats a DATE
or DATETIME
according to the given format expression.
Syntax
DATE_FORMAT(<date>, '<format>')
Parameter | Description |
---|---|
<date> | The date to be formatted. |
<format> | The format to be used for the output using the syntax shown. The reference table below lists allowed expressions and provides example output of each expression for a given date and time. |
Expression for <format> | Description | Expression output for Tuesday the 2nd of April, 1975 at 12:24:48:13 past midnight |
---|---|---|
%C | The year divided by 100 and truncated to integer (00-99) | 19 |
%d | Day of the month, zero-padded (01-31) | 02 |
%D | Short MM/DD/YY date, equivalent to %m/%d/%y | 04/02/75 |
%e | Day of the month, space-padded ( 1-31) | 2 |
%F | Short YYYY-MM-DD date, equivalent to %Y-%m-%d | 1975-04-02 |
%H | The hour in 24h format (00-23) | 00 |
%I | The hour in 12h format (01-12) | 12 |
%j | Day of the year (001-366) | 112 |
%m | Month as a decimal number (01-12) | 04 |
%M | Minute (00-59) | 24 |
%n | New-line character (‘’) in order to add a new line in the converted format. | For example, %Y%n%m returns: 1975 04 |
%p | AM or PM designation | PM |
%R | 24-hour HH:MM time, equivalent to %H:%M | 00:24 |
%S | The second (00-59) | 48 |
%T | ISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S | 00:24:48 |
%u | ISO 8601 weekday as number with Monday as 1 (1-7) | 2 |
%V | ISO 8601 week number (01-53) | 17 |
%w | weekday as a decimal number with Sunday as 0 (0-6) | 2 |
%y | Year, last two digits (00-99) | 75 |
%Y | Year | 1975 |
%% | Escape character to use a % sign | % |
Example
The examples below use a table date_test
with the columns and values below. The following examples use these TIMESTAMP
values to demonstrate the various DATE_FORMAT
expressions.
Category | sale_datetime |
---|---|
a | 2017-06-15 09:34:21 |
b | 2014-01-15 12:14:46 |
c | 1999-09-15 11:33:21 |
The example below shows output for <format>
expressions %C, %d, %D, %e, %F, %H, %I
SELECT
category,
DATE_FORMAT(sale_datetime, '%C') AS C,
DATE_FORMAT(sale_datetime, '%d') AS d,
DATE_FORMAT(sale_datetime, '%D') AS D,
DATE_FORMAT(sale_datetime, '%e') AS e,
DATE_FORMAT(sale_datetime, '%F') AS F,
DATE_FORMAT(sale_datetime, '%H') AS H,
DATE_FORMAT(sale_datetime, '%I') AS I
FROM
date_test
ORDER BY
category;
Returns:
+----------+---------------------+----+----+----------+----+------------+----+----+
| category | sale_datetime | C | d | D | e | F | H | I |
| a | 2017-06-15 09:34:21 | 20 | 15 | 06/15/17 | 15 | 2017-06-15 | 09 | 09 |
| b | 2014-01-15 12:14:46 | 20 | 15 | 01/15/14 | 15 | 2014-01-15 | 12 | 12 |
| c | 1999-09-15 11:33:21 | 19 | 15 | 09/15/99 | 15 | 1999-09-15 | 11 | 11 |
+----------+---------------------+----+----+----------+----+------------+----+----+
The example below shows output for <format>
expressions %j, %m, %M, %p, %R, %S
SELECT
category,
sale_datetime,
DATE_FORMAT(sale_datetime, '%j') AS j,
DATE_FORMAT(sale_datetime, '%m') AS m,
DATE_FORMAT(sale_datetime, '%M') AS M,
DATE_FORMAT(sale_datetime, '%p') AS p,
DATE_FORMAT(sale_datetime, '%R') AS R,
DATE_FORMAT(sale_datetime, '%S') AS S
FROM
date_test
ORDER BY
category;
Returns:
+----------+---------------------+-----+----+----+----+-------+----+
| category | sale_datetime | j | m | M | p | R | S |
| a | 2017-06-15 09:34:21 | 166 | 06 | 34 | AM | 09:34 | 21 |
| b | 2014-01-15 12:14:46 | 015 | 01 | 14 | PM | 12:14 | 46 |
| c | 1999-09-15 11:33:21 | 258 | 09 | 33 | AM | 11:33 | 21 |
+----------+---------------------+-----+----+----+----+-------+----+
The example below shows output for <format>
expressions %T, %u, %V, %w, %y, %Y, %%
SELECT
category,
sale_datetime,
DATE_FORMAT(sale_datetime, '%T') AS T,
DATE_FORMAT(sale_datetime, '%u') AS u,
DATE_FORMAT(sale_datetime, '%V') AS V,
DATE_FORMAT(sale_datetime, '%w') AS w,
DATE_FORMAT(sale_datetime, '%y') AS y,
DATE_FORMAT(sale_datetime, '%Y') AS Y,
DATE_FORMAT(sale_datetime, '%%') AS percent
FROM
date_test
ORDER BY
category;
Returns:
+----------+---------------------+----------+---+----+---+----+------+---------+
| category | sale_datetime | T | u | V | w | y | Y | percent |
| a | 2017-06-15 09:34:21 | 09:34:21 | 4 | 24 | 4 | 17 | 2017 | % |
| b | 2014-01-15 12:14:46 | 12:14:46 | 3 | 03 | 3 | 14 | 2014 | % |
| c | 1999-09-15 11:33:21 | 11:33:21 | 3 | 37 | 3 | 99 | 1999 | % |
+----------+---------------------+----------+---+----+---+----+------+---------+