Literals, or constants, are values coded directly in the text of an SQL statement, view or macro definition text, or CHECK constraint definition text. In general, the system is able to determine the data type of a literal by its form.
A numeric literal (also referred to as a constant) is a character string of 1 to 40characters selected from the following:
- Digits 0 through 9
- Plus sign
- Minus sign
- Decimal point
There are three types of numeric literals: integer, decimal, and floating point.
|Integer Literal||An integer literal declares literal strings of integer numbers. Integer literals consist of an optional sign followed by a sequence of up to 10 digits.
A numeric literal that is outside the range of values of an INTEGER is considered a decimal literal.
Hexadecimal integer literals also represent integer values. A hexadecimal integer literal specifies a string of 0 to 62000 hexadecimal digits enclosed with apostrophes followed by the characters XI1 for a BYTEINT, XI2 for a SMALLINT, XI4 for an INTEGER, or XI8 for a BIGINT.
|Decimal Literal||A decimal literal declares literal strings of decimal numbers.
Decimal literals consist of the following components, reading from left-to-right: an optional sign, an optional sequence of up to 38 digits (mandatory only when no digits appear after the decimal point), an optional decimal point, an optional sequence of digits (mandatory only when no digits appear before the decimal point). The scale and precision of a decimal literal are determined by the total number of digits in the literal and the number of digits to the right of the decimal point, respectively.
|Floating Point Literal||A floating point literal declares literal strings of floating point numbers.
Floating point literals consist of the following components, reading from left-to-right: an optional sign, an optional sequence of digits (mandatory only when no digits appear after the decimal point) representing the whole number portion of the mantissa, an optional decimal point, an optional sequence of digits (mandatory only when no digits appear before the decimal point) representing the fractional portion of the mantissa, the literal character E, an optional sign, a sequence of digits representing the exponent.
Date and time literals declare date, time, or timestamp values in a SQL expression, view or macro definition text, or CONSTRAINT definition text.
Date and time literals are introduced by keywords. For example:
There are three types of DateTime literals: DATE, TIME, and TIMESTAMP.
|DATE Literal||A date literal declares a date value in ANSI DATE format. ANSI DATE literal is the preferred format for DATE constants. All DATE operations accept this format.|
|TIME Literal||A time literal declares a time value and an optional time zone offset.|
|TIMESTAMP Literal||A timestamp literal declares a timestamp value and an optional time zone offset.|
Interval literals provide a means for declaring spans of time.
Interval literals are introduced and followed by keywords. For example:
INTERVAL '200' HOUR
There are two mutually exclusive categories of interval literals: Year-Month and Day-Time.
||Represent a time span that can include a number of years and months.|
||Represent a time span that can include a number of days, hours, minutes, or seconds.|
A character literal declares a character value in an expression, view or macro definition text, or CHECK constraint definition text.
|Character literal||Character literals consist of 0 to 31000bytes delimited by a matching pair of apostrophes. A zero-length character literal is represented by two consecutive apostrophes ('').|
|Hexadecimal character literal||A hexadecimal character literal specifies a string of 0 to 62000 hexadecimal digits enclosed with apostrophes followed by the characters XCF for a CHARACTER data type or XCV for a VARCHAR data type.|
|Unicode character string literal||Unicode character string literals consist of 0 to 31000 Unicode characters and are useful for inserting character strings containing characters that cannot generally be entered directly from a keyboard.|
A graphic literal specifies multibyte characters within the graphic repertoire.
A period literal specifies a constant value of a Period data type. Period literals are introduced by the PERIOD keyword. For example:
PERIOD '(2008-01-01, 2008-02-01)'
The element type of a period literal (DATE, TIME, or TIMESTAMP) is derived from the format of the DateTime values specified in the string literal.
For more information about:
- Data type conversions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.
- Query expressions, see SQL Data Manipulation Language , B035-1146 .
- Numeric, DateTime, interval, character, graphic, period, object name and hexadecimal literals, see SQL Data Types and Literals, B035-1143.
SQL Functions, Operators, Expressions, and Predicates, B035-1145 for the following:
- aggregate functions
- arithmetic expressions
- built-in functions
- byte functions
- calendar functions
- CASE expressions
- comparison operators and functions
- DateTime functions and expressions
- interval expressions
- logical predicates
- ordered analytical functions
- set operators
- string operators and functions
- user-defined functions