TO_CHAR

Converts a TIMESTAMP or a NUMERIC data type to a formatted string.

Syntax

TO_CHAR(<expr>[, '<format>'])

Parameter	Description
`<expr>`	An expression that resolves to a value with a `TIMESTAMP` or `NUMERIC` data type, which will be converted to a string.
`<format>`	One or more optional format element(s) for datetime values.

For descriptions of the accepted <format> options, see below.

Format option	Description
HH24	Hour of day (0-23).
MI	Minute (0-59).
YYYY	4-digit year.
Q	Quarter of year (1-4; January - March = 1).
SS	Second (0-59).
Day	Name of day.
Month	Name of month.
DDD	Day of year (1-366).
DD	Day of month (1-31).
D	Day of week (1-7).
IYYY	4-digit year based on the ISO standard.
IY	Last 2 digits of ISO year.
YY	Last 2 digits of year.
ID	Day of week (1-7) based on the ISO standard.
IW	Week of year (1-53) based on the ISO standard.

Examples

The examples below use a table, time_test, with the columns and values below.

CREATE FACT TABLE time_test (
    id TEXT,
    order_time DATETIME
    )
    PRIMARY INDEX id;

INSERT INTO time_test VALUES
    ('1', '2017-06-15 09:34:21'),
    ('2', '2014-01-15 12:14:46'),
    ('3', '1999-09-15 11:33:21');

The example below shows output for <format> expressions for HH24, MI, YYYY, Q, and SS.

SELECT
    id,
    order_time,
    TO_CHAR(order_time, 'HH24') as HH24,
    TO_CHAR(order_time, 'MI') as MI,
    TO_CHAR(order_time, 'YYYY') as YYYY,
    TO_CHAR(order_time, 'Q') as Q,
    TO_CHAR(order_time, 'SS') as SS
FROM
    time_test
ORDER BY
    id;

Returns

+----+---------------------+------+----+------+---+----+
| id |     order_time      | HH24 | MI | YYYY | Q | SS |
+----+---------------------+------+----+------+---+----+
|  1 | 2017-06-15 09:34:21 |   09 | 34 | 2017 | 2 | 21 |
|  2 | 2014-01-15 12:14:46 |   12 | 14 | 2014 | 1 | 46 |
|  3 | 1999-09-15 11:33:21 |   11 | 33 | 1999 | 3 | 21 |
+----+---------------------+------+----+------+---+----+

The example below shows output for <format> expressions for Day, Month, DDD, DD, and D.

SELECT
    id,
    order_time,
    TO_CHAR(order_time, 'Day') as Day,
    TO_CHAR(order_time, 'Month') as Month,
    TO_CHAR(order_time, 'DDD') as DDD,
    TO_CHAR(order_time, 'DD') as DD,
    TO_CHAR(order_time, 'D') as D
FROM
    time_test
ORDER BY
    id;

Returns

+----+---------------------+-----------+-----------+-----+----+---+
| id |     order_time      |    Day    |   Month   | DDD | DD | D |
+----+---------------------+-----------+-----------+-----+----+---+
|  1 | 2017-06-15 09:34:21 | Thursday  | June      | 166 | 15 | 5 |
|  2 | 2014-01-15 12:14:46 | Wednesday | January   | 015 | 15 | 4 |
|  3 | 1999-09-15 11:33:21 | Wednesday | September | 258 | 15 | 4 |
+----+---------------------+-----------+-----------+-----+----+---+

The example below shows output for <format> expressions for IYYY, IY, YY, ID, and IW.

SELECT
    id,
    order_time,
    TO_CHAR(order_time, 'IYYY') as IYYY,
    TO_CHAR(order_time, 'IY') as IY,
    TO_CHAR(order_time, 'YY') as YY,
    TO_CHAR(order_time, 'ID') as ID,
    TO_CHAR(order_time, 'IW') as IW
FROM
    time_test
ORDER BY
    id;

Returns

+----+---------------------+------+----+----+----+----+
| id |     order_time      | IYYY | IY | YY | ID | IW |
+----+---------------------+------+----+----+----+----+
|  1 | 2017-06-15 09:34:21 | 2017 | 17 | 17 |  4 | 24 |
|  2 | 2014-01-15 12:14:46 | 2014 | 14 | 14 |  3 | 03 |
|  3 | 1999-09-15 11:33:21 | 1999 | 99 | 99 |  3 | 37 |
+----+---------------------+------+----+----+----+----+