TO_CHAR function for converting and formatting data types to strings - MaxCompute

Converts values of different data types to the STRING type. Converts BOOLEAN, BIGINT, DECIMAL, or DOUBLE values to strings, and formats date values into strings based on a specified format.

Syntax

-- Convert a non-date value to a string
STRING TO_CHAR(BOOLEAN|BIGINT|DOUBLE|DECIMAL <value>)

-- Convert a DATE or DATETIME value to a formatted string
STRING TO_CHAR(DATE|DATETIME <date>, STRING <format>)

-- Convert a TIMESTAMP or TIMESTAMP_NTZ value to a formatted string with optional time zone
STRING TO_CHAR(TIMESTAMP|TIMESTAMP_NTZ <date>, STRING <format>[, STRING <time_zone>])

Usage notes

If the input value is NULL in the non-date signature, NULL is returned.
If date is NULL in the date signatures, an error is returned.
If format is NULL, NULL is returned.
In MaxCompute projects that use the version 1.0 data type edition, if the date parameter is a STRING type that matches the yyyy-mm-dd hh:mi:ss format, such as 2025-07-21 00:00:00, it is implicitly converted to the DATETIME type.
When the input is a TIMESTAMP type and you do not specify a time zone, the current session or project time zone is used by default.
When the input is a TIMESTAMP_NTZ type and you do not specify a time zone, the UTC time zone is used by default.

Non-date type conversion

Converts a BOOLEAN, BIGINT, DECIMAL, or DOUBLE value to the STRING type.

Syntax

STRING TO_CHAR(BOOLEAN|BIGINT|DOUBLE|DECIMAL <value>)

-- Standard example.
-- Returns 123.
SELECT TO_CHAR(123);

Parameters

Required:

value: A value of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type.

Return value

Returns a value of the STRING type. The following rules apply:

If value is not a BOOLEAN, BIGINT, DECIMAL, or DOUBLE type, an error is returned.
If value is NULL, NULL is returned.

Examples

Example 1: Convert a value to the STRING type.
```
-- Returns 123.
SELECT TO_CHAR(123);
```
Example 2: If the input parameter is NULL, the return value is NULL.
```
-- Returns NULL.
SELECT TO_CHAR(NULL);
```

Date type conversion

Formats a date or time value into a STRING value based on a specified format and time zone.

Syntax

Two signatures are available depending on the input data type.

Signature 1: Converts a DATE or DATETIME value to a STRING value based on a specified format.

STRING TO_CHAR(DATE|DATETIME <date>, STRING <format>)

-- Standard example.
-- Returns 2025-03-05.
SELECT TO_CHAR(DATETIME '2025-03-05 14:30:00', 'yyyy-mm-dd');

Signature 2: Converts a TIMESTAMP or TIMESTAMP_NTZ value to a STRING value based on a specified format and time zone.

STRING TO_CHAR(TIMESTAMP|TIMESTAMP_NTZ <date>, STRING <format>[, STRING <time_zone>])

-- Standard example.
-- Returns 2025-07-20 07:30:00.
SELECT TO_CHAR(TIMESTAMP '2025-07-20 07:30:00', '%Y-%m-%d %H:%M:%S');

Parameters

Required:

date: The date value to format. The supported data types are DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.
In MaxCompute projects that use the version 1.0 data type edition, if this parameter is a STRING type that matches the yyyy-mm-dd hh:mi:ss format, such as 2025-07-21 00:00:00, it is implicitly converted to the DATETIME type.
format: A STRING that specifies the output format. For the supported format specifiers, see Date format specifiers and Time format specifiers.

Optional:

time_zone: A STRING that specifies the time zone. This parameter is available only for the TIMESTAMP and TIMESTAMP_NTZ types. For more information, see Time zone list.
If you do not specify a time zone:
- If date is a TIMESTAMP type, the current session or project time zone is used by default.
- If date is a TIMESTAMP_NTZ type, the UTC time zone is used by default.

Format specifiers

Date format specifiers

Specifier	Description
`%Y` / `yyyy` / `YYYY`	The year with the century as a decimal number.
`%y`	The year without the century as a decimal number (00-99).
`%m` / `mm` / `MM`	The month as a decimal number (01-12).
`%B`	The full month name in English, such as January.
`%b` / `%h`	The abbreviated month name in English, such as Jan.
`%d` / `dd` / `DD`	The day of the month as a decimal number (01-31).
`%e`	The day of the month as a decimal number (1-31). For example, for the second day of the month, this format returns 2, whereas `dd` returns 02.
`%F`	Equivalent to `%Y-%m-%d` (`yyyy-mm-dd`).
`%j`	The day of the year as a decimal number (001-366), such as 360.
`%u`	The day of the week as a decimal number (1-7), where Monday is 1.
`%A`	The full weekday name in English, such as Wednesday.
`%a`	The abbreviated weekday name in English, such as Wed.

Time format specifiers

Specifier	Description
`%p`	AM or PM.
`%H` / `hh` / `HH`	The hour (24-hour clock) as a decimal number (00-23).
`%I`	The hour (12-hour clock) as a decimal number (01-12).
`%l`	The hour (12-hour clock) as a decimal number (1-12). A leading space is used for single-digit hours.
`%M` / `mi` / `MI`	The minute as a decimal number (00-59).
`%S` / `ss` / `SS`	The second as a decimal number (00-60).
`%X`	Equivalent to the time in `HH:MM:SS` format.
`%T`	Equivalent to `%H:%M:%S` (`hh:mi:ss`).
`ff3`	The millisecond as a three-digit decimal number (000-999). This represents the fractional part of a second.
`%E<number>S`	The second with a fractional precision of `<number>` digits. For example, `%E3S` matches `00.123`.
`%E*S`	The second with a full fractional precision of 6 digits, such as `00.123456`.
`%Ez`	The numeric time zone (`+HH:MM` or `-HH:MM`).

Return value

Returns a STRING value. The following rules apply:

If date is not a DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ type, an error is returned.
If date is NULL, an error is returned.
If format is NULL, NULL is returned.

Examples

Formatting DATE and DATETIME values

Example 1: The input parameter is a DATE or DATETIME type.

-- Returns Today is 2023-12-25.
SELECT TO_CHAR(DATE "2023-12-25", "Today is yyyy-mm-dd");

-- Returns Today is 20231225.
SELECT TO_CHAR(DATE "2023-12-25", "Today is %Y%m%d");

-- Returns Today is 231225Monday.
SELECT TO_CHAR(DATE "2023-12-25", "Today is %y%m%d%A");

-- Returns Today is 12 Month 25 Day.
SELECT TO_CHAR(DATE "2023-12-25", "Today is MM Month DD Day");

-- Returns Alibaba Finance 2010-12*03.
SELECT TO_CHAR(DATETIME '2010-12-03 00:00:00','Alibaba Finance yyyy-mm*dd');

-- Returns The time is 15:30:00.
SELECT TO_CHAR(DATETIME '2025-07-18 15:30:00','The time is %X');

-- Returns It is now 3:30:00 PM.
SELECT TO_CHAR(DATETIME '2025-07-18 15:30:00','It is now %l:%M:%S %p');

-- Returns The hour part is 03.
SELECT TO_CHAR(DATETIME '2025-07-18 15:30:00','The hour part is %I');

-- Returns Day 189 of year 25, July(Jul|Jul), day 08(8), Tuesday(Tue), 15:30:00 PM.
SELECT TO_CHAR(DATETIME '2025-07-08 15:30:00','Day %j of year %y, %B(%b|%h), day dd(%e), %A(%a), %T %p');

-- Returns The date 2025-07-08 is the 2nd day of the week.
SELECT TO_CHAR(DATETIME '2025-07-08 15:30:00','The date %F is the %u day of the week');

-- Returns NULL.
SELECT TO_CHAR(DATE "2023-12-25", NULL);

Implicit STRING-to-DATETIME conversion (version 1.0 data type edition)

The following example demonstrates the implicit type conversion that occurs in MaxCompute projects that use the version 1.0 data type edition. If the date parameter is a STRING type and its format matches the yyyy-mm-dd hh:mi:ss format, it is implicitly converted to the DATETIME type.

Example 2: The input parameter is a STRING type.

-- Set the data type edition to 1.0.
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- The input parameter is a STRING type that matches the DATETIME format.
-- Returns 20250718.
SELECT TO_CHAR('2025-07-18 00:00:00', 'yyyymmdd');

Formatting TIMESTAMP values with time zone conversion

When the parameter is a TIMESTAMP type, you can specify a time zone. If you do not specify a time zone, the current session or project time zone is used by default.

Example 3: The input parameter is a TIMESTAMP type.

-- The current project time zone is Asia/Shanghai.
SET odps.sql.timezone=Asia/Shanghai;

-- Returns 2025-07-20 07:30:00.123456.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00.123456", "%Y-%m-%d %H:%M:%E*S");

-- Returns 2025-07-20 07:30:00+08:00.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00", "%Y-%m-%d %H:%M:%S%Ez");

-- Returns 2025-07-20 07:30:00.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00", "%Y-%m-%d %H:%M:%S", "Asia/Shanghai");

-- Returns 20250720 06:30:00.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00", "yyyymmdd hh:mi:ss", "Asia/Bangkok");

-- Returns 2025-07-20 09:30:00.123.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00.123456789", "%Y-%m-%d %H:%M:%S.ff3", "Australia/Sydney");

-- Returns 2025-07-19 23:30:00.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00.123456", "%Y-%m-%d %H:%M:%S", "Etc/GMT");

-- Returns 2025-07-19 23:30:00Saturday.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00.123456", "%Y-%m-%d %H:%M:%S%A", "Etc/GMT");

-- Returns NULL.
SELECT TO_CHAR(TIMESTAMP "2025-07-20 07:30:00.123456", NULL, "Etc/GMT");

Formatting TIMESTAMP_NTZ values

When the parameter is a TIMESTAMP_NTZ type, you can specify a time zone. If you do not specify a time zone, the UTC time zone is used by default.

Example 4: The input parameter is a TIMESTAMP_NTZ type.

-- Returns The date and time is 2025-07-20 07:30:00.123456.
SELECT TO_CHAR(TIMESTAMP_NTZ "2025-07-20 07:30:00.123456" ,"The date and time is %Y-%m-%d %H:%M:%E6S");

-- Returns 2025-07-20 07:30:00.
SELECT TO_CHAR(TIMESTAMP_NTZ "2025-07-20 07:30:00", "%Y-%m-%d %H:%M:%S", "Etc/GMT");

-- Returns 2025-07-20 Sunday 15:30:00.
SELECT TO_CHAR(TIMESTAMP_NTZ "2025-07-20 07:30:00", "yyyy-mm-dd %A hh:mi:ss", "Asia/Shanghai");

-- Returns 2025-07-20 14:30:00 and the millisecond part is 123.
SELECT TO_CHAR(TIMESTAMP_NTZ "2025-07-20 07:30:00.123456789", "yyyy-mm-dd hh:mi:ss and the millisecond part is ff3", "Asia/Bangkok");

-- Returns NULL.
SELECT TO_CHAR(TIMESTAMP_NTZ "2025-07-20 07:30:00",  NULL);

Related functions

TO_CHAR is a date function and a string function. The following resources provide information about related functions:

TO_DATE -- The inverse operation that converts a string to a date value.
CAST -- A general-purpose type conversion function.
Date functions -- For more information about functions related to date calculations and conversions.
String functions -- For more information about functions for searching for and converting strings.