“Shorthand” Syntax

Procedural SQL (PSQL) StatementsAggregate Functions

“Shorthand” 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	Since Firebird 4.0, the use of `'NOW'`, `'YESTERDAY'` and `'TOMORROW'` in the shorthand cast is no longer allowed;only literals defining a fixed moment in time are supported.

Allowed Type Conversions

The following table shows the type conversions possible with CAST.

Table 1. Possible Type-castings with `CAST`
From	To
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)”.