FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

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.