Most SQL queries contain a data type, for example, a CHAR or VARCHAR data type in a string function SUBSTR[ING]. If you wish to use a different data type, it must be converted first using the explicit conversion functions. For more information, see Conversion Functions.
Data types can be converted either explicitly or implicitly. If explicit conversion functions are not used, then the system attempts to perform an implicit conversion. If an implicit function is not possible or if one single value is not successfully converted during the computation, the system displays an error message.
The following table provides you with an overview of the permissible implicit conversions.
| Source\Target Data Type | DECIMAL | DOUBLE | BOOLEAN | DATE/ TIMESTAMP |
INTERVAL YEAR[(p)] TO MONTH | INTERVAL DAY[(p)] TO SECOND[(fp)] | GEOMETRY | HASHTYPE | CHAR/ VARCHAR |
|---|---|---|---|---|---|---|---|---|---|
|
DECIMAL |
|
|
|
|
|
|
|
|
|
|
DOUBLE |
|
|
|
|
|
|
|
|
|
|
BOOLEAN |
|
|
|
|
|
|
|
|
|
|
DATE/TIMESTAMP |
|
|
|
|
|
|
|
|
|
|
INTERVAL YEAR[(p)] TO MONTH |
|
|
|
|
|
|
|
|
|
|
INTERVAL DAY [(p)] TO SECOND[(fp)] |
|
|
|
|
|
|
|
|
|
|
GEOMETRY |
|
|
|
|
|
|
|
|
|
|
CHAR/VARCHAR |
|
|
|
|
|
|
|
|
|
|
|
- Implicit conversion possible, but errors may occur if single values cannot be converted |
|
|
- Indicates implicit conversion always works |
|
|
- Indicates implicit conversion is not possible |
Conversion Considerations
Below are some of the conversion behaviors exhibited by their corresponding data types:
- When converting DECIMAL to DOUBLE, inaccuracies may occur with rounding.
- When converting a DECIMAL to a DECIMAL with a lower scale (including the INTEGER alias), the value is rounded to the scale specified. For example, SELECT CAST(5.56 AS DECIMAL(3,1)); will return 5.6.
- When converting DECIMAL or DOUBLE to BOOLEAN, 1 is transformed to TRUE, 0 is transformed to FALSE, and NULL is transformed to NULL. For other data types, you can use the compatible representations of the values 1 and 0. For example, 1 = 1.00 (DECIMAL(3.2)) = 1.0 (DECIMAL(2.1)).
- When converting from BOOLEAN to DECIMAL or DOUBLE, TRUE is transformed to 1, FALSE is transformed to 0, and NULL is transformed to NULL.
- When converting from BOOLEAN to CHAR(n) or VARCHAR(n), TRUE is transformed to 'TRUE', and FALSE is transformed to 'FALSE'.
- The conversion of GEOMETRY object with different spacial reference systems is not supported.
- The conversion from CHAR(n) or VARCHAR(n) functions if n is large enough and all characters exist in the target character set.
- The conversion from CHAR(n) or VARCHAR(n) to another data type is successful only if the data to be converted conforms to the target data type.
- When converting from CHAR(n) or VARCHAR(n) to DATE or TIMESTAMP, you must take into account the current format model. For more information , see Date/Time Format Model section.
- When converting from CHAR(n) or VARCHAR(n) to BOOLEAN, you can use strings '0', 'F', 'f' 'N' 'n' or 'FALSE' (case-insensitive) for value FALSE and strings '1', 'T', 't' 'y' 'Y' or 'TRUE' (case-insensitive) for value TRUE.
- When there are operations with multiple operands (for example, the operators +,-,/,*), Exasol implicitly converts the operand to the data type with higher precedence (for example, DOUBLE is higher than DECIMAL) before executing the operation. This rule is also called numeric precedence.
Example
In the following example, implicit conversion is conducted to insert the BOOLEAN entry into the DECIMAL column of the created table.
CREATE TABLE t(d DECIMAL);
-- Implicit conversion from BOOLEAN to DECIMAL
INSERT INTO t VALUES TRUE, NULL, FALSE;
SELECT * FROM t;
