This topic describes how to use type conversion functions in queries through specific examples.
Type conversion functions
Type conversion functions can convert one data type to another, such as converting between numeric and time types, and between string and time types.
Examples
Convert a time string to a time type
Time strings can be directly assigned to the DATE type. OceanBase AI Database can automatically convert them to the time type. You can also use the CONVERT() or CAST() function for data type conversion. An example SQL statement is as follows:
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 a time type to a string type.
Convert between numeric and string types
You can use the CONVERT() or CAST() function to convert between numeric and string types.
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