Data type conversions

Type conversions can happen automatically, or they can be explicitly requested using the CAST or CONVERT function. The following functions can also be used to force type conversions:

• DATE function

  Converts the expression into a DATE, and removes any hours, minutes or seconds. Conversion errors can be reported.

• DATETIME function

  Converts the expression into a TIMESTAMP, and removes any time zone. Conversion errors can be reported.

• STRING function

  This function is equivalent to CAST(value AS LONG VARCHAR).

• VALUE+0.0

  Equivalent to CAST( value AS DECIMAL ).

The following list is a high-level view of automatic data type conversions:

• If a string is used in a numeric expression or as an argument to a function that expects a numeric argument, the string is converted to a number.

• If a number is used in a string expression or as a string function argument, it is converted to a string before being used.

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

Some data types, such as the TABLE REF data type (for use only with variables), cannot be converted to or from another data type.

There are certain cases where the automatic database conversions are not appropriate. For example, the automatic data type conversion fails in the example below.

'12/31/90' + 5
'a' > 0