CAST()

Available in: DSQL, PSQL, ESQL

Changed in: 2.0

Description

CAST converts an expression to the desired datatype. If the conversion is not possible, an error is thrown.

Result type: User-chosen.

Syntax

CAST (expression AS datatype)

Shorthand syntax

Alternative syntax, supported only when casting a string literal to a DATE, TIME or TIMESTAMP:

datatype 'date/timestring'

This syntax was already available in InterBase, but was never properly documented.

Examples

A full-syntax cast:

select cast ('12' || June || '1959' as date) from rdb$database

A shorthand string-to-date cast:

update People set AgeCat = 'Old'
  where BirthDate < date '1-Jan-1943'

Notice that you can drop even the shorthand cast from the example above, as the engine will understand from the context (comparison to a DATE field) how to interpret the string:

update People set AgeCat = 'Old'
  where BirthDate < '1-Jan-1943'

But this is not always possible. The CAST below cannot be dropped, otherwise the engine would find itself with an integer to be subtracted from a string:

select date 'today' - 7 from rdb$database

The following table shows the type conversions possible with CAST.

Table 11.1. Possible CASTs

Keep in mind that sometimes information gets lost, for instance when you cast a TIMESTAMP to a DATE. Also, the fact that types are CAST-compatible is in itself no guarantee that a conversion will succeed. CAST(123456789 as SMALLINT) will definitely result in an error, as will CAST('Judgement Day' as DATE).

New in Firebird 2.0: You can now cast statement parameters to a datatype, like in:

cast (? as integer)

This gives you control over the type of input field set up by the engine. Please notice that with statement parameters, you always need a full-syntax cast – shorthand casts are not supported.