FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

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

Implicit Data Type Conversion

Implicit data conversion is not possible in Dialect 3 — the CAST function is almost always required to avoid data type clashes.

In Dialect 1, in many expressions, one type is implicitly cast to another without the need to use the CAST function.For instance, the following statement in Dialect 1 is valid:

UPDATE ATABLE
  SET ADATE = '25.12.2016' + 1

The string literal will be cast to the DATE type implicitly.

In Dialect 3, this statement will raise error 35544569, “Dynamic SQL Error: expression evaluation not supported, Strings cannot be added or subtracted in dialect 3” — a cast will be needed:

UPDATE ATABLE
  SET ADATE = CAST ('25.12.2016' AS DATE) + 1

Or, with a datetime literal:

UPDATE ATABLE
  SET ADATE = DATE '25.12.2016' + 1

In Dialect 1, mixing integer data and numeric strings is usually possible because the parser will try to cast the string implicitly.For example,

2 + '1'

will be executed correctly.

In Dialect 3, an expression like this will raise an error, so you will need to write it as a CAST expression:

2 + CAST('1' AS SMALLINT)

The exception to the rule is during string concatenation.

Implicit Conversion During String Concatenation

When multiple data elements are being concatenated, all non-string data will undergo implicit conversion to string, if possible.

Example
SELECT 30||' days hath September, April, June and November' CONCAT$
  FROM RDB$DATABASE;

CONCAT$
------------------------------------------------
30 days hath September, April, June and November

Custom Data Types — Domains

In Firebird, the concept of a “user-defined data type” is implemented in the form of the domain.Creating a domain does not truly create a new data type, of course.A domain provides the means to encapsulate an existing data type with a set of attributes and make this “capsule” available for reuse across the whole database.If several tables need columns defined with identical or nearly identical attributes, a domain makes sense.

Domain usage is not limited to column definitions for tables and views.Domains can be used to declare input and output parameters and variables in PSQL code.