CAST Expressions

Use the CAST (... AS ...) keywords or the double-colon ( :: ) cast operator to convert the data type of the value of an expression to some other target data type.

If an SQL statement incudes the cast operator or the CAST (... AS ...) keywords, the database server examines the syscasts system catalog table for an existing cast corresponding to the data types of the expression value and the target data types that the Cast Expression specifies. If no built-in cast, explicit cast, or implicit cast registered in the system catalog can perform the specified conversion, the SQL statement returns an error.

(1)
Cast Expressions

1  CAST ( expression?  +  :: target_type AS target_type )
1  expression +  :: target_type
Notes:

Element Description Restrictions Syntax
expression Expression whose data type will be replaced by a target_type Must evaluate to an atomic, or JSON document, or large object, or ROW data type Expression
target_type Data type replacing the type returned by expression (or by an intermediate cast) See Rules for the Target Data Type Data Type

Usage

A non-recursive cast expression converts a data value from its current data type directly to a specified target data type.

To execute an SQL statement that incudes the cast operator or the CAST (... AS ...) keywords, the database server searches the syscasts system catalog table for an existing cast corresponding to
  • the data type of the expression value in the syscasts.argument_type column,
  • and the target data type that the Cast Expression specifies in the syscasts.result_type column.
If no built-in cast, explicit cast, or implicit cast registered in the syscasts table matches these criteria for performing the specified conversion, the database server returns an error.

In this case, you might consider whether a recursive CAST expression is required, using intermediate data types. You might also consider whether you can use the CREATE CAST statement to define and register in the system catalog an appropriate new explicit or implicit cast for data-type conversion to the target type.

For examples of explicit cast expressions for target types of non-opaque, user-defined, and large object data types, see Examples of Cast Expressions.

Recursive CAST expressions

As the syntax diagram indicates, however, the ::target_type syntax can be used recursively, so that the value of the original source expression is cast to one or more successive intermediate data types before it is cast to the last target type.

For example, the following expression casts a DATETIME (YEAR TO DAY) value to MONEY(16,2), using DATE, INTEGER, and DECIMAL as intermediate data types:
CAST (CURRENT::DATE::INTEGER::DECIMAL AS MONEY(16,2))
The next CAST expression, without the CASE or AS keywords, is logically equivalent: 
CURRENT::DATE::INTEGER::DECIMAL::MONEY(16,2)
Tip: Use caution before interpreting this syntax example, which was designed to illustrate multiple casts within a single CAST expression, as validation of "Time is money" economic theories.