Updated on 2025-08-25 GMT+08:00

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
postgres=#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
postgres=#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
postgres=#SELECT cast('a' as int4);
      int4
---------------

(1 row)
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#SELECT to_char(current_timestamp,'HH12:MI:SS');
 to_char  
----------
 10:19:26
(1 row)
1
2
3
4
5
postgres=#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
postgres=#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
postgres=#SELECT to_char(1485,'9,999');
 to_char 
---------
  1,485
(1 row)
1
2
3
4
5
postgres=#SELECT to_char( 1148.5,'9,999.999');
  to_char   
------------
  1,148.500
(1 row)
1
2
3
4
5
postgres=#SELECT to_char(148.5,'990999.909');
   to_char   
-------------
    0148.500
(1 row)
1
2
3
4
5
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#SELECT to_clob('ABCDEF'::RAW(10));
 to_clob 
---------
 ABCDEF
(1 row)
1
2
3
4
5
postgres=#SELECT to_clob('hello111'::CHAR(15));
 to_clob  
----------
 hello111
(1 row)
1
2
3
4
5
postgres=#SELECT to_clob('gauss123'::NCHAR(10));
 to_clob  
----------
 gauss123
(1 row)
1
2
3
4
5
postgres=#SELECT to_clob('gauss234'::VARCHAR(10));
 to_clob  
----------
 gauss234
(1 row)
1
2
3
4
5
postgres=#SELECT to_clob('gauss345'::VARCHAR2(10));
 to_clob  
----------
 gauss345
(1 row)
1
2
3
4
5
postgres=#SELECT to_clob('gauss456'::NVARCHAR2(10));
 to_clob  
----------
 gauss456
(1 row)
1
2
3
4
5
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#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
postgres=#SHOW nls_timestamp_format;
    nls_timestamp_format    
----------------------------
 DD-Mon-YYYY HH:MI:SS.FF AM
(1 row)

postgres=#SELECT to_timestamp('12-sep-2014');
    to_timestamp     
---------------------
 2014-09-12 00:00:00
(1 row)
1
2
3
4
5
postgres=#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
postgres=#SELECT to_timestamp('-1','SYYYY');
      to_timestamp      
------------------------
 0001-01-01 00:00:00 BC
(1 row)
1
2
3
4
5
postgres=#SELECT to_timestamp('98','RR');
    to_timestamp     
---------------------
 1998-01-01 00:00:00
(1 row)
1
2
3
4
5
postgres=#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
postgres=#SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
    to_timestamp
---------------------
 2000-12-05 00:00:00
(1 row)

Table 1 shows the template patterns that can be used to format numeric value, for the function to_number.

Table 1 Template patterns for numeric formatting

Pattern

Description

9

Value with the specified number of numeric value digits

0

Value with leading zeros

. (period)

Decimal point

, (comma)

Grouping (thousand) separator

PR

Negative value in angle brackets

S

Signed numeric value (uses locale)

L

Currency symbol (uses locale)

D

Decimal point (uses locale)

G

Group separator (uses locale)

MI

Minus sign in the specified position (if number < 0)

PL

Plus sign in the specified position (if number > 0)

SG

Plus/minus sign in the specified position

RN

Roman numeral (input between 1 and 3999)

TH or th

Ordinal suffix

V

Shift specified digits (decimal)

Table 2 shows the templates you can use to format date and time values. These patterns apply to the functions to_date, to_timestamp, to_char, and the parameter nls_timestamp_format.

Table 2 Patterns for date and time formatting

Category

Pattern

Description

Hour

HH

Hour of the day (01–12)

HH12

Hour of the day (01–12)

HH24

Hour of the day (00–23)

Minute

MI

Minute (00–59)

Second

SS

Second (00–59)

FF

Microsecond (000000–999999)

SSSSS

Second after midnight (0–86399)

AM or PM

AM or A.M.

AM indicator

PM or P.M.

PM indicator

Year

Y,YYY

Year with comma (4 or more digits)

SYYYY

Four-digit BC year

YYYY

Year (4 or more digits)

YYY

Last three digits of the year

YY

Last two digits of the year

Y

Last one digit of the year

IYYY

ISO year (4 or more digits)

IYY

Last three digits of the ISO year

IY

Last two digits of the ISO year

I

Last one digit of the ISO year

RR

Last two digits of the year (can store 20th-century years in the 21st century)

Rules are as follows:

  • Entered two-digit year is between 00 and 49:

    If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are the same as the first two digits of the current year.

    If the last two digits of the current year are between 50 and 99, the first two digits of the returned year are the first two digits of the current year plus 1.

  • Entered two-digit year is between 50 and 99:

    If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are the first two digits of the current year minus 1.

    If the last two digits of the current year are between 50 and 99, the first two digits of the returned year are the same as the first two digits of the current year.

RRRR

Can accept 4-digit years or 2-digit years. If it is two digits, the value is the same as the return value of RR. If it is four digits, the value is the same as the return value of YYYY.

  • BC or B.C.
  • AD or A.D.

Era indicators. Before Christ (BC) and Anno Domini (AD).

Month

MONTH

Full-length uppercase month name (blank-padded to 9 characters)

MON

Abbreviated uppercase month name (3 characters)

MM

Month number (01–12)

RM

Roman numeral month (I-XII; I=JAN) (uppercase)

Day

DAY

Full-length uppercase day name (blank-padded to 9 characters)

DY

Abbreviated uppercase day name (3 characters)

DDD

Day of the year (001–366)

DD

Day of the month (01–31)

D

Day of the week (1–7; Sunday is 1)

Week

W

Week of the month (1–5) (First week starts on the first day of the month)

WW

Week of the year (1–53) (First week starts on the first day of the year)

IW

ISO week of the year (First Thursday is in the first week)

Century

CC

Century (2 digits) (21st century starts from 2001-01-01)

Julian day

J

Julian day (days since January 1, 4712 BC)

Quarter

Q

Quarter