All Products
Search

This Product

    MaxCompute:TO_CHAR

    Document Center

    MaxCompute:TO_CHAR

    Last Updated:Jun 24, 2026

    Converts BOOLEAN, BIGINT, DECIMAL, or DOUBLE values to strings, and formats date and time values as strings based on a specified format.

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

    • 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 a string.

      -- Returns 123.
SELECT TO_CHAR(123);

    • Example 2: NULL input returns 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 based on the input type.

    • Signature 1: Convert a DATE or DATETIME value to a string.

      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: Convert a TIMESTAMP or TIMESTAMP_NTZ value to a string with optional 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. Supported types: DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

      In MaxCompute projects that use the version 1.0 data type edition, a STRING value matching the yyyy-mm-dd hh:mi:ss format (for example, 2025-07-21 00:00:00) is implicitly converted to DATETIME.

    • format: A STRING that specifies the output format. For the supported format specifiers, see Date format specifiersand 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 时区列表.

      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 HH:MM:SS.

    %T

    Equivalent to %H:%M:%S (hh:mi:ss).

    ff3

    Milliseconds as a three-digit decimal number (000-999).

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

    • 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: DATE and DATETIME input.

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

    In MaxCompute projects using the version 1.0 data type edition, a STRING parameter matching the yyyy-mm-dd hh:mi:ss format is implicitly converted to DATETIME.

    • Example 2: STRING input (implicit conversion).

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

    For TIMESTAMP input, you can specify a time zone. Defaults to the session or project time zone.

    • Example 3: TIMESTAMP input.

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

    For TIMESTAMP_NTZ input, you can specify a time zone. Defaults to UTC.

    • Example 4: TIMESTAMP_NTZ input.

      -- 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 both a date function and a string 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.