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`.

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)

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

SELECT CAST(my_date AS SQL_CHAR) FROM my_table

SELECT CONVERT(my_char, SQL_INTEGER) FROM my_table