8.5Type Casting Functions

8.5.1CAST()

Available inDSQL, ESQL, PSQL

Changed in2.5

Syntax

  |CAST (<expression> AS <target_type>)
  | 
  |<target_type>  ::=
  |      <sql_datatype>
  |    | [TYPE OF] domain
  |    | TYPE OF COLUMN relname.colname

Table 8.49CAST Function Parameters
ParameterDescription

expression

SQL expression

sql_datatype

SQL data type

domain

 

relname

Table or view name

colname

Table or view column name

Result typeUser-chosen.

DescriptionCAST converts an expression to the desired datatype or domain. If the conversion is not possible, an error is raised.

8.5.1.1Shorthand 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. In the SQL standard, this feature is called datetime literals.

Note

The short syntax is evaluated immediately at parse time, causing the value to stay the same until the statement is unprepared. For datetime literals like '12-Oct-2012' this makes no difference. For the pseudo-variables 'NOW', 'YESTERDAY', 'TODAY' and 'TOMORROW', this may not be what you want. If you need the value to be evaluated at every call, use the full CAST() syntax.

8.5.1.2Cast 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 8.50Possible Type-castings with CAST
FromTo

Numeric types

Numeric types [VAR]CHAR BLOB

[VAR]CHAR BLOB

[VAR]CHAR BLOB Numeric types DATE TIME TIMESTAMP

DATE TIME

[VAR]CHAR BLOB TIMESTAMP

TIMESTAMP

[VAR]CHAR BLOB 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).

Casting input fieldsSince Firebird 2.0, you can cast statement parameters to a datatype:

  |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.

Casting to a domain or its typeFirebird 2.1 and above support casting to a domain or its base type. When casting to a domain, any constraints (NOT NULL and/or CHECK) declared for the domain must be satisfied or the cast will fail. Please be aware that a CHECK passes if it evaluates to TRUE or NULL! So, given the following statements:

  |create domain quint as int check (value >= 5000);
  |select cast (2000 as quint) from rdb$database;     
  |select cast (8000 as quint) from rdb$database;     
  |select cast (null as quint) from rdb$database;     

only cast number 1 will result in an error.

When the TYPE OF modifier is used, the expression is cast to the base type of the domain, ignoring any constraints. With domain quint defined as above, the following two casts are equivalent and will both succeed:

  |select cast (2000 as type of quint) from rdb$database;
  |select cast (2000 as int) from rdb$database;

If TYPE OF is used with a (VAR)CHAR type, its character set and collation are retained:

   |create domain iso20 varchar(20) character set iso8859_1;
   |create domain dunl20 varchar(20) character set iso8859_1 collate du_nl;
   |create table zinnen (zin varchar(20));
   |commit;
   |insert into zinnen values ('Deze');
   |insert into zinnen values ('Die');
   |insert into zinnen values ('die');
   |insert into zinnen values ('deze');
   | 
   |select cast(zin as type of iso20) from zinnen order by 1;
   |-- returns Deze -> Die -> deze -> die
   | 
   |select cast(zin as type of dunl20) from zinnen order by 1;
   |-- returns deze -> Deze -> die -> Die
🛑
Warning

If a domain’s definition is changed, existing CASTs to that domain or its type may become invalid. If these CASTs occur in PSQL modules, their invalidation may be detected. See the note The RDB$VALID_BLR field, in Appendix A.

Casting to a column’s typeIn Firebird 2.5 and above, it is possible to cast expressions to the type of an existing table or view column. Only the type itself is used; in the case of string types, this includes the character set but not the collation. Constraints and default values of the source column are not applied.

  |create table ttt (
  |  s varchar(40) character set utf8 collate unicode_ci_ai
  |);
  |commit;
  | 
  |select cast ('Jag har många vänner' as type of column ttt.s)
  |from rdb$database;
🛑
Warnings
  • For text types, character set and collation are preserved by the cast — just as when casting to a domain. However, due to a bug, the collation is not always taken into consideration when comparisons (e.g. equality tests) are made. In cases where the collation is of importance, test your code thoroughly before deploying! This bug is fixed for Firebird 3.

  • If a column’s definition is altered, existing CASTs to that column’s type may become invalid. If these CASTs occur in PSQL modules, their invalidation may be detected. See the note The RDB$VALID_BLR field, in Appendix A.

Casting BLOBsSuccessful casting to and from BLOBs is possible since Firebird 2.1.