Shorthand Casts For Datetime Data Types

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.