Firebird Documentation IndexFirebird 2.0 Language Ref. UpdateInternal functions → CAST()
Firebird Home Firebird Home Prev: BIT_LENGTH()Firebird Documentation IndexUp: Internal functionsNext: CHAR_LENGTH(), CHARACTER_LENGTH()

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 To
Numeric types
Numeric types
[VAR]CHAR
[VAR]CHAR
[VAR]CHAR
Numeric types
DATE
TIME
TIMESTAMP
DATE
TIME
[VAR]CHAR
TIMESTAMP
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.

Prev: BIT_LENGTH()Firebird Documentation IndexUp: Internal functionsNext: CHAR_LENGTH(), CHARACTER_LENGTH()
Firebird Documentation IndexFirebird 2.0 Language Ref. UpdateInternal functions → CAST()