solidDB Help : solidDB reference : ODBC: Scalar functions : Explicit data type conversion
  
Explicit data type conversion
Explicit data type conversion is specified in terms of SQL data type definitions.
The ODBC syntax for the explicit data type conversion function does not restrict conversions. The validity of specific conversions of one data type to another data type is dependent on each driver-specific implementation. The driver, as it translates the ODBC syntax into the native syntax, reject those conversions that, although legal in the ODBC syntax, are not supported by the data source. Applications can call the ODBC function SQLGetInfo to inquire about conversions supported by the data source.
The format of the CONVERT function is:
CONVERT(value_exp, data_type)
The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords:
SQL_BIGINT
SQL_SMALLINT
SQL_BINARY
SQL_DATE
SQL_CHAR
SQL_TIME
SQL_DECIMAL
SQL_TIMESTAMP
SQL_DOUBLE
SQL_TINYINT
SQL_FLOAT
SQL_VARBINARY
SQL_INTEGER
SQL_VARCHAR
SQL_LONGVARBINARY
SQL_WCHAR
SQL_LONGVARCHAR
SQL_WLONGVARCHAR
SQL_NUMERIC
SQL_WVARCHAR
SQL_REAL
The ODBC syntax for the explicit data type conversion function does not support specification of conversion format. If specification of explicit formats is supported by the underlying data source, a driver must specify a default value or implement format specification.
The argument value_exp can be a column name, the result of another scalar function, or a numeric or string literal. The following example converts the output of the CURDATE scalar function to a character string:
{ fn CONVERT( { fn CURDATE() }, SQL_CHAR) }
ODBC does not require a data type for return values from scalar functions (because the functions are often data source-specific); applications should use the CONVERT scalar function whenever possible to force data type conversion.
The following two examples illustrate the use of the CONVERT function. These examples assume the existence of a table called EMPLOYEES, with an EMPNO column of type SQL_SMALLINT and an EMPNAME column of type SQL_CHAR.
If an application executes the following statement:
SELECT EMPNO FROM EMPLOYEES WHERE {fn CONVERT(EMPNO,SQL_CHAR)}LIKE '1%'
solidDB ODBC driver translates the request to:
SELECT EMPNO FROM EMPLOYEES WHERE CONVERT_CHAR(EMPNO) LIKE '1%'
Go up to
ODBC: Scalar functions