15.00 - Implicit Type Conversion of Comparison Operands - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Implicit Type Conversion of Comparison Operands

Expression operands must be of the same data type before a comparison operation can occur.

Data Types on Which Implicit Conversion is Performed

If operand data types differ, Teradata Database performs an implicit conversion according to the following table. Implicit conversions are Teradata extensions to the ANSI SQL:2011 standard.

Comparisons between character and numeric data types require that the character field be convertible to a numeric value.

 

IF one expression operand is …

AND the other expression operand is …

THEN Teradata Database compares the data as …

Character

Character

Character.

For more details, see “Character String Comparisons” on page 505.

Date

Date.

Teradata Database returns an error for character data with GRAPHIC server character set.

BYTEINT
SMALLINT
INTEGER
FLOAT

FLOAT.

Teradata Database returns an error for character data with GRAPHIC server character set.

Period

Period.

CHAR(k)
VARCHAR(k))

WHERE k <= 16

BIGINT
DECIMAL(
m,n)

FLOAT.

Teradata Database returns an error for character data with GRAPHIC server character set.

CHAR(k)
VARCHAR(k)

where k > 16

 

DECIMAL(m,n)

where m <= 16

FLOAT.

Teradata Database returns an error for character data with GRAPHIC server character set.

BIGINT
DECIMAL(
m,n)

where m > 16

Teradata Database returns an error.

CHAR(k)
VARCHAR(k)

NUMBER and k <=16

FLOAT.

Teradata Database returns an error for character data with GRAPHIC server character set.

NUMBER and k > 16

Teradata Database returns an error.

BYTEINT

SMALLINT

SMALLINT.

BYTEINT
SMALLINT

INTEGER

INTEGER.

BYTEINT
SMALLINT
INTEGER
BIGINT

BIGINT

BIGINT.

BYTEINT

DECIMAL(m,n)

where m <= 18 and m-n >= 3

DECIMAL(18,n).

 

 

 

SMALLINT

DECIMAL(m,n)

where m <= 18 and m-n >= 5

INTEGER

DECIMAL(m,n)

where m <= 18 and m-n >= 10

DATE

BYTEINT

DECIMAL(m,n)

where m > 18 or m-n < 3

DECIMAL(38,n).

 

 

 

SMALLINT

DECIMAL(m,n)

where m > 18 or m-n < 5

INTEGER

DECIMAL(m,n)

where m > 18 or m-n < 10

DATE

BIGINT

DECIMAL(m,n)

DECIMAL(m,n)

 

DECIMAL(k,j)

where max(m-n,k-j) + max(j,n) <= 18

DECIMAL(18,max(j,n)).

DECIMAL(k,j)

where max(m-n,k-j) + max(j,n) > 18

DECIMAL(38,max(j,n)).

BYTEINT
SMALLINT
INTEGER
BIGINT
DECIMAL(
m,n)
NUMBER(m,n)
NUMBER(m)
NUMBER(*,n)
NUMBER

NUMBER(k,j)
NUMBER(k)
NUMBER(*,j)
NUMBER

NUMBER

DATE

BYTEINT
SMALLINT
INTEGER

INTEGER.

BIGINT

BIGINT.

FLOAT

FLOAT.

FLOAT

BYTEINT
SMALLINT
INTEGER
BIGINT
DECIMAL(
m,n)
NUMBER(m,n)
NUMBER(m)
NUMBER(*,n)
NUMBER

FLOAT.

Period

Character

Period.

Implicit Conversion of DateTime Types

In comparisons involving DateTime operands that differ, Teradata Database performs an implicit conversion according to the following table.

 

IF one expression operand is …

AND the other expression operand is …

THEN Teradata Database compares the data as …

TIMESTAMP

DATE1

 

DATE.

See “Implicit TIMESTAMP-to-DATE Conversion” on page 734.

TIMESTAMP WITH TIME ZONE

Interval2

Exact Numeric

Numeric.

See “Implicit INTERVAL-to-Numeric Conversion” on page 661.


1
ANSIDate dateform mode or IntegerDate dateform mode

2
The INTERVAL type must have only one field, e.g. INTERVAL YEAR.

Data Types on Which Implicit Conversion is Not Performed

The following table identifies data types on which Teradata Database does not perform implicit type conversion.

 

Type

Rules

Byte

Byte data types can only be compared with byte data types. Attempts to compare a byte type with another type produces an error.

TIME

Teradata Database does not perform implicit type conversion from TIME to TIMESTAMP and from TIMESTAMP to TIME in comparison operations.

TIMESTAMP

UDT

Teradata Database does not perform implicit type conversion on UDTs for comparison operations. A UDT value can only be compared with another value of the same UDT type.

To compare UDTs with other data types, you must use explicit data type conversion. For more information, see Chapter 13: “Data Type Conversions.”