17.10 - Literals - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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.

Numeric Literals

A numeric literal (also referred to as a constant) is a character string of 1 to 40 characters 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.

Type Description
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.

DateTime Literals

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:

DATE '1969-12-23'

There are three types of DateTime literals: DATE, TIME, and TIMESTAMP.

Type Description
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

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.

Category Type Description
Year-Month
  • YEAR
  • YEAR TO MONTH
  • MONTH
Represent a time span that can include a number of years and months.
Day-Time
  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND
Represent a time span that can include a number of days, hours, minutes, or seconds.

Character Literals

A character literal declares a character value in an expression, view or macro definition text, or CHECK constraint definition text.

Type Description
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.

Graphic Literals

A graphic literal specifies multibyte characters within the graphic repertoire.

Period Literals

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.

Related Information

For more information about:
  • Data type conversions, see Teradata Vantage™ - Data Types and Literals, B035-1143.
  • Query expressions, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • Numeric, DateTime, interval, character, graphic, period, object name and hexadecimal literals, see Teradata Vantage™ - Data Types and Literals, B035-1143.
  • Calendar functions, see Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.
  • DateTime functions and expressions, see Teradata Vantage™ - Data Types and Literals, B035-1143.
  • Set operators, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • See Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for the following:
    • aggregate functions
    • arithmetic expressions
    • built-in functions
    • byte functions
    • CASE expressions
    • comparison operators and functions
    • interval expressions
    • logical predicates
    • ordered analytical functions
    • string operators and functions
    • user-defined functions