FirebirdSQL logo
Hexadecimal Value Ranges
  • Hex numbers in the range 0 …​ 7FFF FFFF are positive INTEGERs with values between 0 …​ 2147483647 decimal.To coerce a number to BIGINT, prepend enough zeroes to bring the total number of hex digits to nine or above.That changes the type but not the value.

  • Hex numbers between 8000 0000 …​ FFFF FFFF require some attention:

    • When written with eight hex digits, as in 0x9E44F9A8, a value is interpreted as 32-bit INTEGER.Since the leftmost bit (sign bit) is set, it maps to the negative range -2147483648 …​ -1 decimal.

    • With one or more zeroes prepended, as in 0x09E44F9A8, a value is interpreted as 64-bit BIGINT in the range 0000 0000 8000 0000 …​ 0000 0000 FFFF FFFF.The sign bit is not set now, so they map to the positive range 2147483648 …​ 4294967295 decimal.

    Thus, in this range, and for 16 vs 16+ digits, prepending a mathematically insignificant 0 results in a different value.This is something to be aware of.

  • Hex numbers between 0 0000 0001 …​ 7FFF FFFF FFFF FFFF are all positive BIGINT.

  • Hex numbers between 8000 0000 0000 0000 …​ FFFF FFFF FFFF FFFF are all negative BIGINT.

  • Hex numbers between 0 0000 0000 0000 0001 …​ 7FFF FFFF FFFF FFFF FFFF FFFF FFFF FFFF are all positive INT128

  • Hex numbers between 8000 0000 0000 0000 0000 0000 0000 0000 …​ FFFF FFFF FFFF FFFF FFFF FFFF FFFF FFFF are all negative INT128

  • A SMALLINT cannot be written in hex, strictly speaking, since even 0x0 and 0x1 are evaluated as INTEGER.However, if you write a positive integer within the 16-bit range 0x0000 (decimal zero) to 0x7FFF (decimal 32767) it will be converted to SMALLINT transparently.

    It is possible to write to a negative SMALLINT in hex, using a 4-byte hex number within the range 0xFFFF8000 (decimal -32768) to 0xFFFFFFFF (decimal -1).

Boolean Literals

A Boolean literal is one of TRUE, FALSE or UNKNOWN.

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.