Data Type Conversions

Type conversions happen automatically, or you can explicitly request them using the CAST or CONVERT function.

Usage

If a string is used in a numeric expression or as an argument to a function expecting a numeric argument, the string is converted to a number before use.

If a number is used in a string expression or as a string function argument, then the number is converted to a string before use.

All date constants are specified as strings. The string is automatically converted to a date before use.

There are certain cases where the automatic data type conversions are not appropriate.

'12/31/90' + 5 -- Tries to convert the string to a number
'a' > 0        -- Tries to convert 'a' to a number

You can use the CAST or CONVERT function to force type conversions.

The following functions can also be used to force type conversions:

DATE( expression ) – converts the expression into a date, and removes any hours, minutes or seconds. Conversion errors might be reported.
DATETIME( expression ) – converts the expression into a timestamp. Conversion errors might be reported.
STRING( expression ) – similar to CAST(value AS CHAR), except that string(NULL) is the empty string (''), whereas CAST(NULL AS CHAR) is the NULL value.

Note: If a problem occurs when converting TINYINT, SMALLINT, INT, DOUBLE, DATE, DATETIME, TIME, BIGINT, UNSIGNED BIGINT, UNSIGNED INT to CHAR or VARCHAR, SAP Sybase IQ logs the failure as an error or warning in the .iqmsg file. If the CONVERSION_ERROR option is ON, SAP Sybase IQ reports the problem as an error. If the CONVERSION_ERROR option is OFF, the problem is reported as a warning