FirebirdSQL logo

Casting to TYPE OF COLUMN

When operands are cast to the type of a column, the specified column may be from a table or a view.

Only the type of the column itself is used.For character types, the cast includes the character set, but not the collation.The constraints and default values of the source column are not applied.

Example
CREATE TABLE TTT (
  S VARCHAR (40)
  CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);
COMMIT;

SELECT
  CAST ('I have many friends' AS TYPE OF COLUMN TTT.S)
FROM RDB$DATABASE;

Conversions Possible for the CAST Function

Table 1. Conversions with CAST
From Data Type To Data Type

Numeric types

Numeric types, [VAR]CHAR, BLOB

[VAR]CHAR

[VAR]CHAR, BLOB, Numeric types, DATE, TIME, TIMESTAMP, BOOLEAN

BLOB

[VAR]CHAR, BLOB, Numeric types, DATE, TIME, TIMESTAMP, BOOLEAN

DATE, TIME

[VAR]CHAR, BLOB, TIMESTAMP

TIMESTAMP

[VAR]CHAR, BLOB, DATE, TIME

BOOLEAN

BOOLEAN, [VAR]CHAR, BLOB

To convert string data types to the BOOLEAN type, the value must be (case-insensitive) 'true' or 'false', or NULL.

Important

Keep in mind that partial information loss is possible.For instance, when you cast the TIMESTAMP data type to the DATE data type, the time-part is lost.

Datetime Formats

To cast string data types to the DATE, TIME or TIMESTAMP data types, you need the string argument to be one of the predefined datetime mnemonics (see [fblangref50-dtyp-tbl-datetimemnemonics]) or a representation of the date in one of the allowed datetime formats (see Datetime Format Syntax),

Table 1. Predefined Datetime Mnemonics

Literal

Description

'NOW'

Current date and time

'TODAY'

Current date

'TOMORROW'

Current date + 1 (day)

'YESTERDAY'

Current date - 1 (day)

Note

Casting the date mnemonics 'TODAY', 'TOMORROW' or 'YESTERDAY' to a TIMESTAMP WITH TIME ZONE will produce a value at 00:00:00 UTC rebased to the session time zone.

For example cast('TODAY' as timestamp with time zone) on 2021-05-02 20:00 - 2021-05-03 19:59 New York (or 2021-05-03 00:00 - 2021-05-03 23:59 UTC) with session time zone America/New_York will produce a value TIMESTAMP '2021-05-02 20:00:00.0000 America/New_York', while cast('TODAY' as date) or CURRENT_DATE will produce either DATE '2021-05-02' or DATE '2021-05-03' depending on the actual date.

Sample Date Literal Interpretations
select
  cast('04.12.2014' as date) as d1, -- DD.MM.YYYY
  cast('04 12 2014' as date) as d2, -- MM DD YYYY
  cast('4-12-2014' as date) as d3,  -- MM-DD-YYYY
  cast('04/12/2014' as date) as d4, -- MM/DD/YYYY
  cast('04.12.14' as date) as d5,   -- DD.MM.YY
  -- DD.MM with current year
  cast('04.12' as date) as d6,
  -- MM/DD with current year
  cast('04/12' as date) as d7,
  cast('2014/12/04' as date) as d8, -- YYYY/MM/DD
  cast('2014 12 04' as date) as d9, -- YYYY MM DD
  cast('2014.12.04' as date) as d10, -- YYYY.MM.DD
  cast('2014-12-04' as date) as d11, -- YYYY-MM-DD
  cast('4 Jan 2014' as date) as d12, -- DD MM YYYY
  cast('2014 Jan 4' as date) as dt13, -- YYYY MM DD
  cast('Jan 4 2014' as date) as dt14, -- MM DD YYYY
  cast('11:37' as time) as t1, -- HH:mm
  cast('11:37:12' as time) as t2, -- HH:mm:ss
  cast('11:31:12.1234' as time) as t3, -- HH:mm:ss.nnnn
  -- DD.MM.YYYY HH:mm
  cast('04.12.2014 11:37' as timestamp) as dt1,
  -- MM/DD/YYYY HH:mm:ss
  cast('04/12/2014 11:37:12' as timestamp) as dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn
  cast('04.12.2014 11:31:12.1234' as timestamp) as dt3,
  cast('now' as timestamp) as m1,
  cast('today' as date) as m2,
  cast('yesterday' as date) as m3,
  cast('tomorrow' as date) as m4
from rdb$database

Shorthand Casts for Datetime Data Types

Firebird allows the use of a shorthand “C-style” type syntax for casts from string to the types DATE, TIME and TIMESTAMP.The SQL standard calls these “datetime literals”.

Syntax
<data_type> 'date_format_string'

See also Datetime Literals.

Note

These literal expressions are evaluated directly during parsing, as though the statement were already prepared for execution.As this produced unexpected or confusing results when using the datetime mnemonics like 'NOW', especially in PSQL code, the datetime mnemonics are no longer allowed in datetime literals since Firebird 4.0.

To use datetime mnemonics, use the full CAST syntax.An example of using such an expression in a trigger:

NEW.CHANGE_DATE = CAST('now' AS TIMESTAMP);