17.00 - 17.05 - Comparison Operators - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

The following table describes the comparison operators.

Operator Purpose
EQ or = Assume p1 and p2 are Period expressions and have comparable Period data types.

If BEGIN(p1) = BEGIN(p2) AND END(p1) = END(p2), the result of the comparison is TRUE; otherwise, the result is FALSE.

If either Period expression is NULL, the result is UNKNOWN.

If the Period expressions have different element types, one of them must be explicitly CAST as the other.

If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

EQ supports comparisons between derived periods.

LT or < Assume p1 and p2 are Period expressions and have comparable Period data types.

If BEGIN(p1) < BEGIN(p2) OR (BEGIN(p1) = BEGIN(p2) AND END(p1) < END(p2)), the result of the comparison is TRUE; otherwise, the result is FALSE.

If either Period expression is NULL, the result is UNKNOWN.

If the Period expressions have different element types, one of them must be explicitly CAST as the other.

If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR operand is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

If the ending bound value of a system-versioned system-time or temporal table transaction-time column is UNTIL_CLOSED, the ending bound value is only less than a TIMESTAMP column value or TIMESTAMP literal if the column value or literal is the maximum TIMESTAMP value with leap seconds. This can be possible only if the ending bound of the system-versioned system-time or transaction-time column is used in a comparison with the timestamp value. For more information about temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 or Teradata Vantage™ - Temporal Table Support , B035-1182 .

LT supports comparisons between derived periods.

GT or > Assume p1 and p2 are Period expressions and have comparable Period data types.

If BEGIN(p1) > BEGIN(p2) OR (BEGIN(p1) = BEGIN(p2) AND END(p1) > END(p2)), the result of the comparison is TRUE; otherwise, it is FALSE.

If either Period expression is NULL, the result is UNKNOWN.

If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR Period expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

GT supports comparisons between derived periods.

NE or <> or
NOT= or ^= or
LE or <= or 
GE or >= These comparison operators are supported for comparable Period expressions.

If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR Period expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

NE, LE, and GE support comparisons between derived periods.