Explicit Data Type Conversion (CAST and CONVERT)
In BBj 7.0 and higher, the BBj SQL engine provides two explicit data type conversion functions shown in the table below.
Function |
Description |
---|---|
CONVERT(expression, data_type) |
Takes the provided expression and attempts to transform it into the specified data_type. If the conversion is possible, it returns the value as the new data type. If the conversion is not logically feasible (e.g., trying to cast the string "hello" to an integer), the query results in an error. Valid data types are: SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY, SQL_BLOB, SQL_BYTE, SQL_INTEGER, SQL_BIGINT, SQL_REAL, SQL_FLOAT, SQL_DOUBLE, SQL_DECIMAL, SQL_NUMERIC, SQL_TIMESTAMP, SQL_TIME, SQL_BIT, SQL_BOOLEAN, SQL_DECIMAL, SQL_NUMERIC, SQL_DATE, and SQL_DATETIME. |
CAST(expression AS data_type) |
Takes the provided expression and attempts to transform it into the specified data_type. If the conversion is possible, it returns the value as the new data type. If the conversion is not logically feasible (e.g., trying to cast the string "hello" to an integer), the query results in an error. Valid data types are: CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, BLOB, BYTE, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, TIMESTAMP, TIME, BIT, BOOLEAN, DECIMAL, NUMERIC, DATE, and DATETIME. |
See Supported Data Types for a list of supported data types for this function.
Example
|
|