<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Manipulation Language > Functions > CAST function

Converts specified value to the specified data type.

CAST(column_reference AS data_type)

Description

Use CAST to convert the value in the specified column to the data type specified. CAST can also be applied to literal and calculated values. CAST can be used in the columns list of a SELECT statement, in the predicate for a WHERE clause, or to modify the update atom of an UPDATE statement.

The Data_Type parameter may be one of most column data type applicable to the table type used: CHAR, INTEGER, NUMERIC, and so on. Certain column types cannot be used as the source or target data types: BLOB, MEMO, and BYTES.

The statement below converts a Paradox TIMESTAMP column value to DATE.

SELECT CAST(SaleDate AS DATE)

FROM ORDERS

Converting a column value with CAST allows use of other functions or predicates on an otherwise incompatible data type, such as using the SUBSTRING function on a DATE column.

SELECT SaleDate,

 SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1)

FROM Orders

When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.

Note: the CAST function cannot be used with memo or BLOB columns.