Use type conversion functions in queries

Type conversion functions convert one data type to another. For example, they support conversion between numbers, time, and strings.

• Example: Convert time strings to datetime data types

  In an Oracle-compatible tenant, you can use the TO_DATE() function to convert strings to datetime data types. The second argument in the TO_DATE() function specifies a datetime format, which must conform to the system date format. The SQL statement is as follows:

  obclient> SELECT to_date('Feb 20, 2020', 'Mon dd, YYYY') t1
          , to_date('18:30:45', 'HH24:MI:SS') t2
          , to_date('2020/11/11 00:30:00', 'YYYY/MM/DD HH24:MI:SS') t3
          FROM dual \G
  *************************** 1. row ***************************
  T1: 20-FEB-20
  T2: 01-JUN-22
  T3: 11-NOV-20
  1 row in set
  

  In an Oracle-compatible tenant, you can use the TO_CHAR() function to convert datetime data types to strings.

• Example: Convert between numbers and strings

  In an Oracle-compatible tenant, you can use the TO_CHAR() function to convert numbers to strings. You can use the TO_NUMBER() function to convert strings to numbers.

  obclient> SELECT to_number('3.14') n1, to_number('-3.14') n2, to_char(3.14159,'99.99') c1
  FROM DUAL;
  +------+-------+--------+
  | N1   | N2    | C1     |
  +------+-------+--------+
  | 3.14 | -3.14 |   3.14 |
  +------+-------+--------+
  1 row in set