This topic provides specific examples on how to use type conversion functions in queries.
Type conversion functions
Type conversion functions convert one data type to another. For example, they support conversion between numbers, time, and strings.
Examples
Convert time strings to datetime data types
In the MySQL compatible mode of OceanBase Cloud, you can directly assign time strings to records of the DATE type. OceanBase Cloud can automatically convert the strings to the DATE type. You can also use the CONVERT() or CAST() function to convert the time strings to the DATE type. Here is an example:
obclient> SELECT CONVERT('2020-02-02 14:30:45', DATE) t1
, CONVERT('2020-02-02 14:30:45', TIME) t2
, CONVERT('2020-02-02 14:30:45', DATETIME) t3
, CAST('2020-02-02 14:30:45' AS DATE) t4
, CAST('2020-02-02 14:30:45' AS TIME) t5
, CAST('2020-02-02 14:30:45' AS DATETIME) t6
\G
*************************** 1. row ***************************
t1: 2020-02-02
t2: 14:30:45
t3: 2020-02-02 14:30:45
t4: 2020-02-02
t5: 14:30:45
t6: 2020-02-02 14:30:45
1 row in set
Note
You can use the DATE_FORMAT() function to convert values of the DATE type to strings.
Convert between numbers and strings
In a MySQL-compatible tenant of OceanBase Cloud, you can use the CONVERT() or CAST() function to implement conversion between numbers and strings.
obclient> SELECT CONVERT('3.1415926', DECIMAL) n1
, CAST('3.1415926' AS DECIMAL) n2
, CONVERT(3.1415926, CHAR(10)) s1
, CAST(3.1414926 AS CHAR(10)) s2;
+------+------+-----------+-----------+
| n1 | n2 | s1 | s2 |
+------+------+-----------+-----------+
| 3 | 3 | 3.1415926 | 3.1414926 |
+------+------+-----------+-----------+
1 row in set