FirebirdSQL logo

Datetime Literals

Formally, the SQL standard defines datetime literals as a prefix DATE, TIME and TIMESTAMP followed by a string literal with a datetime format.Historically, Firebird documentation has referred to these datetime literals as “shorthand casts”.

Since Firebird 4.0, the use of datetime mnemonics in datetime literals (e.g. DATE 'TODAY') is no longer allowed.

Caution

The format of datetime literals and strings in Firebird 4.0 and higher is more strict compared to earlier Firebird versions.

Datetime Literal Syntax
<datetime_literal> ::=
    DATE '<date_format>'
  | TIME { '<time_format>' | '<time_tz_format>' }
  | TIMESTAMP { '<timestamp_format>' | '<timestamp_tz_format>' }
Datetime Format Syntax
<date_format> ::=
      [YYYY<p>]MM<p>DD
    | MM<p>DD[<p>{ YYYY | YY }]
    | DD<p>MM[<p>{ YYYY | YY }]

<time_format> ::= HH[:mm[:SS[<f>NNNN]]]

<timestamp_format> ::= <date_format> [<space> <time_format>]

<time_zone> ::=
    { + | - }HH:MM
  | time zone name (e.g. Europe/Berlin)

<time_tz_format> ::= <time_format> [<space>] <time_zone>

<timestamp_tz_format> ::= <timestamp_format> [<space>] <time_zone>

<p> ::= whitespace | . | - | /
<f> ::= : | .
Table 1. Date and Time Literal Format Arguments
Argument Description

datetime_literal

Datetime literal

date_format

Format of date

time_format

Format of time

timestamp_format

Format of timestamp

time_zone

Format of time zone

time_tz_format

Format of time with time zone

timestamp_tz_format

Format of timestamp with time zone

YYYY

Four-digit year

YY

Two-digit year

MM

MonthIt may contain 1 or 2 digits (1-12 or 01-12).You can also specify the three-letter shorthand name or the full name of a month in English.Case-insensitive

DD

Day.It may contain 1 or 2 digits (1-31 or 01-31)

HH

Hour.It may contain 1 or 2 digits (0-23 or 00-23)

mm

Minutes.It may contain 1 or 2 digits (0-59 or 00-59)

SS

Seconds.It may contain 1 or 2 digits (0-59 or 00-59)

NNNN

Ten-thousandths of a second.It may contain from 1 to 4 digits (0-9999)

p

A date separator, any of permitted characters.Leading and trailing spaces are ignored.The choice of separator in a date decides whether the parser reads MM<p>DD or DD<p>MM.

f

Fractional seconds separator

Important

Use of the complete specification of the year in the four-digit form — YYYY — is strongly recommended, to avoid confusion in date calculations and aggregations.

Example
-- 1
  UPDATE PEOPLE
  SET AGECAT = 'SENIOR'
  WHERE BIRTHDATE < DATE '1-Jan-1943';
-- 2
  INSERT INTO APPOINTMENTS
  (EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME)
  VALUES (973, 8804, DATE '1-Jan-2021' + 2, TIME '16:00');
-- 3
  NEW.LASTMOD = TIMESTAMP '1-Jan-2021 16:00';
Tip

Although the Firebird datetime syntax is flexible, to avoid ambiguity we recommend using the ISO-8601 order (year-month-day), ‘-’ as the date separator, 4 digits for year, 2 digits for month, day, minute and second, : as the time separator, and ‘.’ as second.fractions separator.This format is also the only one defined in the SQL standard.

In short, use TIMESTAMP '2021-05-03 04:05:00.1 +02:00', not TIMESTAMP '3.5.21 4:5:0:1 +2:0'.

SQL Operators

SQL operators comprise operators for comparing, calculating, evaluating and concatenating values.

Operator Precedence

SQL Operators are divided into four types.Each operator type has a precedence, a ranking that determines the order in which operators and the values obtained with their help are evaluated in an expression.The higher the precedence of the operator type is, the earlier it will be evaluated.Each operator has its own precedence within its type, that determines the order in which they are evaluated in an expression.

Operators with the same precedence are evaluated from left to right.To force a different evaluation order, operations can be grouped by means of parentheses.

Table 1. Operator Type Precedence
Operator Type Precedence Explanation

Concatenation

1

Strings are concatenated before any other operations take place

Arithmetic

2

Arithmetic operations are performed after strings are concatenated, but before comparison and logical operations

Comparison

3

Comparison operations take place after string concatenation and arithmetic operations, but before logical operations

Logical

4

Logical operators are executed after all other types of operators

Concatenation Operator

The concatenation operator — two pipe characters known as “double pipe” or ‘||’ — concatenates two character strings to form a single string.Character strings can be literals or values obtained from columns or other expressions.

Example
SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
FROM EMPLOYEE
See also

BLOB_APPEND()