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 | %       |
+----------+---------------------+----------+---+----+---+----+------+---------+