CAST( )
Go Up to Statement and Function Reference (Language Reference Guide)
Converts a column from one data type to another. Available in gpre, DSQL, and isql.
CAST (value AS <data_type>)
| Argument | Description |
|---|---|
| <val> | A column, constant, or expression; in SQL, <val> can also be a host-language variable, function, or UDF. |
| <data_type> | Data type to which to convert. |
Description: CAST() allows mixing of numerics and characters in a single expression by converting val to a specified data type.
Normally, only similar data types can be compared in search conditions. CAST() can be used in search conditions to translate one data type into another for comparison purposes.
Data types can be converted as shown in the following table:
| From data type class | To data type class |
|---|---|
| Numeric | character, varying character, numeric |
| Character, varying character | numeric, date, time, timestamp |
| Date | character, varying character, timestamp |
| Time | character, varying character, timestamp |
| Timestamp | character, varying character, date, time |
| Blob, arrays |
— |
|
Boolean |
character, varying character |
An error results if a given data type cannot be converted into the data type specified in CAST(). For example, you will get a string conversion error if you attempt to cast from a
numeric type which is unable to represent in a date type to a date (e.g. a numeric type attempting to represent "year 99/12/31"(December) or "year 32768/3/1"(March)).
Example: In the following WHERE clause, CAST() is used to translate a CHARACTER data type, INTERVIEW_DATE, to a DATE data type to compare against a DATE data type, HIRE_DATE:
. . . WHERE HIRE_DATE = CAST (INTERVIEW_DATE AS DATE);
To cast a VARCHAR data type, you must specify the length of the string, for example:
UPDATE client SET charef = CAST (clientref AS VARCHAR(20));