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.

The following table shows the type conversions possible with CAST.

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

You can also cast statement parameters to a data type:

cast (? as integer)

This gives you control over the type of the parameter 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 base type are supported. 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

It is also 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;

The FORMAT clause serves two purposes:

1. Converting a datetime value to a string

2. Parsing a string value to a datetime value

The cast_template of the FORMAT clause specifies how the datetime should be converted to string, or how the string should be parsed to a datetime.

The cast_template must be a string literal, and has the following syntax:

Most of these template fields are defined by the SQL Standard, but some are non-standard extensions. The template fields are case-insensitive, so YYYY-MM is the same as yyyy-mm.

The template fields can be separated with the following delimiters:

It is possible to insert raw text into a format string with double quotes: …​ FORMAT '"Today is" DAY' → Today is MONDAY. To render ", escape it with \, i.e. use \", and to render \, use \\.

Example:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(45)
  FORMAT 'DD.MM.YEAR HH24:MI:SS "is" J "Julian day"')
FROM RDB$DATABASE;
=========================
14.6.2023 15:41:29 is 2460110 Julian day

Patterns can be used without any separators:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(50)
  FORMAT 'YEARMMDD HH24MISS')
FROM RDB$DATABASE;
=========================
20230719 161757

However, be careful with patterns with repeating characters, for example DDDDD will be interpreted as DDD + DD.

SELECT CAST('2000.12.08 12:35:30.5000' AS TIMESTAMP
  FORMAT 'YEAR.MM.DD HH24:MI:SS.FF4')
FROM RDB$DATABASE;
=====================
2000-12-08 12:35:30.5000

A full-syntax cast:

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

A shorthand string-to-date cast (a.k.a. date literal):

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'

However, 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 cast('today' as date) - 7 from rdb$database

Render a timestamp in ISO 8601 format:

select cast(localtimestamp as char(24)
  format 'YYYY-MM-DD"T"HH24:MI:SS.FF4')
from rdb$database
-- 2025-07-24T11:18:56.9930