TO_CHAR command format, parameters, and return value - MaxCompute

The TO_CHAR function converts values of different data types to the STRING type. It converts BOOLEAN, BIGINT, DECIMAL, or DOUBLE values to strings. It also formats date values into strings based on a specified format.

BOOLEAN, BIGINT, DECIMAL, and DOUBLE type conversion

You can use the TO_CHAR function to convert 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

value: Required. 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

Basic 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

You can use the TO_CHAR function to format a date or time value into a STRING value based on a specified format and time zone.

Syntax

When you use a date or time data type as an input parameter, the TO_CHAR function supports the following two signatures. Select a signature based on your needs.

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

date: Required. 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: Required. A STRING that specifies the output format. The following tables describe the supported format elements for the date and time parts.
Date format elements
- %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 elements
- %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).
time_zone: Optional. A STRING that specifies the time zone. 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.

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

Basic examples

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);

Example 2: The input parameter is a STRING type.

For 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.

-- 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');

Example 3: The input parameter is a TIMESTAMP type.

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.

-- 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");

Example 4: The input parameter is a TIMESTAMP_NTZ type.

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.

-- 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.

For more information about functions related to date calculations and conversions, see Date functions.
For more information about functions for searching for and converting strings, see String functions.

BOOLEAN, BIGINT, DECIMAL, and DOUBLE type conversion

Syntax

Parameters

Return value

Examples

Basic examples

Date type conversion

Syntax

Parameters

Date format elements

Time format elements

Return value

Examples

Basic examples

Related functions