All Products
Search

This Product

    MaxCompute:TO_CHAR

    Document Center

    MaxCompute:TO_CHAR

    Last Updated:Jun 28, 2023

    Converts different types of data into a string. For data of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type, you can use this function to convert the data into the STRING type. For date values, you can use this function to convert the date values into strings in a specified format.

    String conversion

    Converts data of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type into the STRING type.

    Syntax

    string to_char(boolean <value>)
string to_char(bigint <value>)
string to_char(double <value>)
string to_char(decimal <value>)

    Parameters

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

    Return value

    A value of the STRING type is returned. The return value varies based on the following rules:

    • If value is not of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type, an error is returned.

    • If value is set to null, null is returned.

    Examples

    • Example 1: Convert values into the STRING type. Sample statements: 

      -- The return value is 123. 
select to_char(123);
-- The return value is true. 
select to_char(true);
-- The return value is 1.23. 
select to_char(1.23);

    • Example 2: An input parameter is set to null. Sample statement: 

      -- The return value is null. 
select to_char(null);

    Date conversion

    Converts a date value into a string in a specified format.

    Syntax

    string to_char(datetime <date>, string <format>)

    Parameters

    • date: required. A date value of the DATETIME type. The date value is in the yyyy-mm-dd hh:mi:ss format. If the input value is of the STRING type and the MaxCompute V1.0 data type edition is used in your project, the input value is implicitly converted into the DATETIME type before calculation.

    • format: required. A constant of the STRING type. In the format parameter, the date format part is replaced by the related data and the other characters remain unchanged in the output.

    Return value

    A value of the STRING type is returned. The return value varies based on the following rules:

    • If the value of date is not of the DATETIME or STRING type, an error is returned.

    • If the value of date is null, an error is returned.

    • If the value of format is null, null is returned.

    Sample data

    This section provides sample source data for you to understand how to use date functions. In this topic, a table named mf_date_fun_t is created and data is inserted into the table. Sample commands: 
    create table if not exists mf_date_fun_t(
    id      int,
    date1   date,
    datetime1   datetime,
    timestamp1 timestamp,
    date2   date,
    datetime2   datetime,
    timestamp2 timestamp,
    date3 string,
    date4 bigint);
insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);
    Query data from the mf_date_fun_t table. Sample statement:
    select * from mf_date_fun_t;
-- The following result is returned: 
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| id         | date1      | datetime1           | timestamp1              | date2      | datetime2           | timestamp2              | date3      | date4      |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 1          | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780  |
| 2          | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781  |
| 3          | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782  |
| 4          | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783  |
| 5          | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784  |
| 6          | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785  |
| 7          | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786  |
| 8          | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787  |
| 9          | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788  |
| 10         | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

    Examples

    • Example 1: static data 

      -- The return value is Alibaba Cloud Financial Services 2010-12*03. If the MaxCompute client runs in Windows, Chinese characters may not be properly displayed in the returned result. 
select to_char(datetime'2010-12-03 00:00:00', 'Alibaba Cloud Financial Services yyyy-mm*dd');
-- The return value is 20080718. 
select to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');
-- The return value is 20080718. 
set odps.sql.type.system.odps2=false;
select to_char('2008-07-18 00:00:00', 'yyyymmdd');
-- 'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'. 
select to_char(datetime'Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd');
-- '20102401' is not a standard DATETIME value, and an error is returned. The value must be written as '2010-01-24 00:00:00' 
select to_char(datetime'20102401', 'yyyy');
-- '2008718' is not a standard DATETIME value, and an error is returned. The value must be written as '2008-07-18 00:00:00'. 
select to_char(datetime'2008718', 'yyyymmdd');
-- The return value is null. 
select to_char(datetime'2010-12-03 00:00:00', null);

    • Example 2: table data

      Convert date values in the datetime1 column into strings in a specified format. The sample data is used in this example. Sample statements: 

      select datetime1, to_char(datetime1, 'yyyy-mm-dd') as datetime1_to_char from mf_date_fun_t;

      The following result is returned: 

      +---------------------+-------------------+
| datetime1           | datetime1_to_char |
+---------------------+-------------------+
| 2021-11-29 00:01:00 | 2021-11-29        |
| 2021-11-28 00:02:00 | 2021-11-28        |
| 2021-11-27 00:03:00 | 2021-11-27        |
| 2021-11-26 00:04:00 | 2021-11-26        |
| 2021-11-25 00:05:00 | 2021-11-25        |
| 2021-11-24 00:06:00 | 2021-11-24        |
| 2021-11-23 00:07:00 | 2021-11-23        |
| 2021-11-22 00:08:00 | 2021-11-22        |
| 2021-11-21 00:09:00 | 2021-11-21        |
| 2021-11-20 00:10:00 | 2021-11-20        |
+---------------------+-------------------+

    Related functions

    TO_CHAR is a date function or a string function.

    • For more information about functions related to date computing and conversion, see Date functions.

    • For more information about functions related to string searches and conversion, see String functions.