CAST Function (SQL)

CAST (arg AS type)

The CAST function converts the argument arg to the specified type (explicit conversion, see implicit conversion in the SET statement).

WARNING: This function is not like casting in higher programming languages.

You cannot select the string formatting method when converting a value to a character string. If this is necessary, use the special string conversion functions of 602SQL.

The following errors may occur when executing this function:

When converting a character string to another type, the string value does not represent a value of the target type (see notation on numbers, date and time): error sqlstate 22018
When converting to a number type and the result value cannot be displayed in that type (either too big or too small): error sqlstate 22003
When converting not-string to a character string of a specified length and the converted value length is too long: error sqlstate 22001

These errors may be prevented by specifying settings in the Set_sql_option function (e.g. flag SQLOPT_MASK... (8,16,32)). If these flags are turned on, then the result is NULL in the first two cases, and the string is cut from the right in the third case.

REAL to CHAR(n) conversion result is a semi-logarithmic number (e.g. 1.2345E+4) with a dot as a decimal separator (unless another separator is set by the server property DecimalSeparator).

DATE to CHAR(n) conversion result is in the form 24.12.2002.

TIME to CHAR(n) conversion result is in the form 15:45:02.000

TIMESTAMP to CHAR(n) conversion result is in the form 24.12.2002 15:45:02.

BINARY(n) to CHAR(2*n) conversion result is the hexadecimal notation of binary string

CHAR(n) to REAL has the same rules for converting a string as the Str2real function.

Example:

Convert the expression from NUMERIC to INTEGER

SELECT Companies.company,CAST(SUM(Orders.order) AS INT)
FROM Companies, Orders
WHERE Orders.company=Companies.company
GROUP BY Companies.company

See other conversion functions in 602SQL:

Str2int, Str2bigint, Str2real, Str2date, Str2time, Str2timestamp, Int2str, Bigint2str, Real2str, Date2str, Time2str, Timestamp2str, Datetime2timestamp, Timestamp2date, Timestamp2time, Ord, Chr

COALESCE Function

Functions Defined by the SQL Standard

Aggregate (Group) Functions