This topic describes how to use type conversion functions in queries and provides examples.
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 MySQL mode of OceanBase Database, you can directly copy time strings to records of the DATE type. OceanBase Database 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. Syntax:
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 the MySQL tenant mode of OceanBase Database, 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