Comparison Operators - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

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.