CAST()

Available in: DSQL, ESQL, PSQL

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

From

Numeric types

[VAR]CHAR

Numeric types

DATE

TIME

TIMESTAMP

DATE

TIME

[VAR]CHAR

TIMESTAMP

[VAR]CHAR

DATE

TIME

Keep in mind that sometimes information is 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, as 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.