Type conversion functions convert a data type to another. For example, they support conversion between numbers, time, and strings.
This topic describes how to use type conversion functions in queries and provides examples.
Example 1: Conversion between time strings and the DATE type
In the 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_FORMATfunction to convert values of the DATE type to strings.
Example 2: Conversion 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
