DATE_FORMAT (legacy)

You are looking at legacy documentation for Firebolt’s deprecated date and timestamp type functions. New types were introduced in DB version 3.19 under the names PGDATE and TIMESTAMPNTZ, and made generally available in DB version 3.22.

If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns an error, you are still using the legacy date and timestamp types and can continue with this documentation, or find instructions to use the new types here. If this query returns a result, you are already using the redesigned date and timestamp types and can use the TO_CHAR function instead.

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