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 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.
See also:
UPPER( )
Firebird 2.0.4. Release Notes: CAST() behaviour improved
ibec_Cast
Firebird Null Guide: Internal functions and directives
back to top of page
<< BIT_LENGTH() | FB 2.0 Language Reference | CHAR_LENGTH(), CHARACTER_LENGTH() >>