Type Conversion Functions

cast(x as y)

Description: A type conversion function that converts x into the type specified by y.

Example:

1
2
3
4
5

SELECT cast('22-oct-1997' as timestamp);
      timestamp      
---------------------
 1997-10-22 00:00:00
(1 row)

cast(x, y)

Description: A type conversion function that converts x into the type specified by y. This function is only supported by clusters of version 8.2.0 or later.

Example:

1
2
3
4
5

SELECT cast('22-oct-1997', timestamp);
      timestamp      
---------------------
 1997-10-22 00:00:00
(1 row)

try_cast(x as type)

Description: Converts x to a value of the given type. If the conversion fails and the current type is converted to a value allowed by DataArts Fabric SQL, NULL is returned. Otherwise, an error is raised. This function is only supported by clusters of version 8.2.0 or later.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT cast('a' as int4);
      int4
---------------

(1 row)
SELECT cast('22-oct-1997', timestamp);
      timestamp      
---------------------
 1997-10-22 00:00:00
(1 row)

hextoraw(string)

Description: Converts a string in hexadecimal format into binary format.

Return type: raw.

Example:

1
2
3
4
5

SELECT hextoraw('7D');
 hextoraw 
----------
 7D
(1 row)

numtoday(numeric)

Description: Converts numeric values into timestamps in the specified format.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT numtoday(2);
 numtoday
----------
 2 days
(1 row)

pg_systimestamp()

Description: Retrieves the system timestamp.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT pg_systimestamp();
        pg_systimestamp
-------------------------------
 2015-10-14 11:21:28.317367+08
(1 row)

rawtohex(string)

Description: Converts a string in binary format into a string in hexadecimal format.

The result represents the ASCII codes of the input characters in hexadecimal format.

Return type: varchar.

Example:

1
2
3
4
5

SELECT rawtohex('1234567');
    rawtohex    
----------------
 31323334353637
(1 row)

to_char (datetime/interval [, fmt])

Description: Converts a DATETIME or INTERVAL value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE type to the VARCHAR type according to the format specified by fmt.

• Optional parameter fmt can include categories such as date, time, week, quarter, and century. Each category can have different templates, which can be reasonably combined. Common templates include HH, MM, SS, YYYY, MM, and DD. Refer to Table 2 for details.
• Templates can have modifiers, with FM being a common one used to suppress leading zeros or trailing spaces.

Return type: varchar.

Example:

1
2
3
4
5

SELECT to_char(current_timestamp,'HH12:MI:SS');
 to_char  
----------
 10:19:26
(1 row)

1
2
3
4
5

SELECT to_char(current_timestamp,'FMHH12:FMMI:FMSS');
 to_char  
----------
 10:19:46
(1 row)

to_char(double precision, text)

Description: Converts double-precision values into strings in the specified format.

Return type: text.

Example:

1
2
3
4
5

SELECT to_char(125.8::real, '999D99');
 to_char 
---------
  125.80
(1 row)

to_char (integer/number[, fmt])

Description: Converts integer or floating-point values into strings in the specified format.

• Optional parameter fmt can include categories such as decimal digits, group separators, plus/minus signs, and currency symbols. Each category can have different templates, which can be reasonably combined. Common templates include 9, 0, , (thousands separator), . (decimal point). Refer to Table 1 for details.
• Templates can have modifiers like FM, but FM does not suppress zeros specified by the template 0.
• To convert integer values to their corresponding hexadecimal string representations, use the template x or X.

Return type: varchar.

Example:

1
2
3
4
5

SELECT to_char(1485,'9,999');
 to_char 
---------
  1,485
(1 row)

1
2
3
4
5

SELECT to_char( 1148.5,'9,999.999');
  to_char   
------------
  1,148.500
(1 row)

1
2
3
4
5

SELECT to_char(148.5,'990999.909');
   to_char   
-------------
    0148.500
(1 row)

1
2
3
4
5

SELECT to_char(123,'XXX');
 to_char 
---------
   7B
(1 row)

to_char(interval, text)

Description: Converts the values of the time interval type into the strings in the specified format.

Return type: text.

Example:

1
2
3
4
5

SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');
 to_char
----------
 15:02:12
(1 row)

to_char(int, text)

Description: Converts values of the integer type into strings in the specified format.

Return type: text.

Example:

1
2
3
4
5

SELECT to_char(125, '999');
 to_char
---------
  125
(1 row)

to_char(numeric, text)

Description: Converts the values of the numeric type into the strings in the specified format.

Return type: text.

Example:

1
2
3
4
5

SELECT to_char(-125.8, '999D99S');
 to_char
---------
 125.80-
(1 row)

to_char (string)

Description: Converts the CHAR, VARCHAR, VARCHAR2, or CLOB type to the VARCHAR type.

If this function is used to convert the CLOB type and the value to be converted is not within the range of the target type, an error is returned.

Return type: varchar.

Example:

1
2
3
4
5

SELECT to_char('01110');
 to_char
---------
 01110
(1 row)

to_char(timestamp, text)

Description: Converts the values of the timestamp type into the strings in the specified format.

Return type: text.

Example:

1
2
3
4
5

SELECT to_char(current_timestamp, 'HH12:MI:SS');
 to_char
----------
 10:55:59
(1 row)

to_clob(char/nchar/varchar/nvarchar/varchar2/nvarchar2/text/raw)

Description: Converts the RAW type or text character set type CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 or TEXT to the CLOB type.

Return type: clob.

Example:

1
2
3
4
5

SELECT to_clob('ABCDEF'::RAW(10));
 to_clob 
---------
 ABCDEF
(1 row)

1
2
3
4
5

SELECT to_clob('hello111'::CHAR(15));
 to_clob  
----------
 hello111
(1 row)

1
2
3
4
5

SELECT to_clob('gauss123'::NCHAR(10));
 to_clob  
----------
 gauss123
(1 row)

1
2
3
4
5

SELECT to_clob('gauss234'::VARCHAR(10));
 to_clob  
----------
 gauss234
(1 row)

1
2
3
4
5

SELECT to_clob('gauss345'::VARCHAR2(10));
 to_clob  
----------
 gauss345
(1 row)

1
2
3
4
5

SELECT to_clob('gauss456'::NVARCHAR2(10));
 to_clob  
----------
 gauss456
(1 row)

1
2
3
4
5

SELECT to_clob('World222!'::TEXT);
  to_clob  
-----------
 World222!
(1 row)

to_date(text)

Description: Converts values of the text type into the timestamp in the specified format.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT to_date('2015-08-14');
       to_date
---------------------
 2015-08-14 00:00:00
(1 row)

to_date(text, text)

Description: Converts the values of the string type into the dates in the specified format.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT to_date('05 Dec 2000', 'DD Mon YYYY');
       to_date
---------------------
 2000-12-05 00:00:00
(1 row)

to_date(string, fmt)

Description: Converts a string into a value of the DATE type in the format specified by fmt. For details about the fmt format, see Table 2.

This function cannot directly support the CLOB type, but parameters of the CLOB type can be implicitly converted.

Return type: date.

Example:

1
2
3
4
5

SELECT TO_DATE('05 Dec 2010','DD Mon YYYY');
       to_date       
---------------------
 2010-12-05 00:00:00
(1 row)

to_number ( expr [, fmt])

Description: Converts expr into a value of the NUMBER type in the specified format.

For details about the type conversion format, see Table 1.

When a hexadecimal string is converted into a decimal number, a maximum of 16 bytes of hexadecimal string can be converted into an unsigned number.

When a hexadecimal string is converted into a decimal number, the format string cannot contain other characters except x or X. Otherwise, an error is reported.

Return type: number.

Example:

1
2
3
4
5

SELECT to_number('12,454.8-', '99G999D9S');
 to_number 
-----------
  -12454.8
(1 row)

to_number(text, text)

Description: Converts the values of the string type into the numbers in the specified format.

Return type: numeric.

Example:

1
2
3
4
5

SELECT to_number('12,454.8-', '99G999D9S');
 to_number
-----------
  -12454.8
(1 row)

to_timestamp(double precision)

Description: Converts a UNIX epoch to a timestamp.

Return type: timestamp with time zone.

Example:

1
2
3
4
5

SELECT to_timestamp(1284352323);
      to_timestamp      
------------------------
 2010-09-13 12:32:03+08
(1 row)

to_timestamp(string [,fmt])

Description: Converts a string into a value of the timestamp type based on the format specified by fmt. If fmt is not specified, the format specified by nls_timestamp_format is used. For details about the fmt format, see Table 2.

In to_timestamp of DataArts Fabric SQL:

• If the input year YYYY is 0, the system reports an error.
• If the input year YYYY is less than 0 and SYYYY is specified in fmt, the year corresponding to the absolute value n BC is output correctly.

The characters in the fmt must match the date/time formatting mode. Otherwise, an error is reported.

Return type: timestamp without time zone.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SHOW nls_timestamp_format;
    nls_timestamp_format    
----------------------------
 DD-Mon-YYYY HH:MI:SS.FF AM
(1 row)

SELECT to_timestamp('12-sep-2014');
    to_timestamp     
---------------------
 2014-09-12 00:00:00
(1 row)

1
2
3
4
5

SELECT to_timestamp('12-Sep-10 14:10:10.123000','DD-Mon-YY HH24:MI:SS.FF');
      to_timestamp       
-------------------------
 2010-09-12 14:10:10.123
(1 row)

1
2
3
4
5

SELECT to_timestamp('-1','SYYYY');
      to_timestamp      
------------------------
 0001-01-01 00:00:00 BC
(1 row)

1
2
3
4
5

SELECT to_timestamp('98','RR');
    to_timestamp     
---------------------
 1998-01-01 00:00:00
(1 row)

1
2
3
4
5

SELECT to_timestamp('01','RR');
    to_timestamp     
---------------------
 2001-01-01 00:00:00
(1 row)

to_timestamp(text, text)

Description: Converts a value of the string type to a timestamp in the specified format.

Return type: timestamp.

Example:

1
2
3
4
5

SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
    to_timestamp
---------------------
 2000-12-05 00:00:00
(1 row)