15.00 - Binary Arithmetic Result Data Types - 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)

Binary Arithmetic Result Data Types

The data type of the result of an arithmetic expression depends on the data types of the two operands. Operands are converted to the result type before the operation is performed.

For example, before an INTEGER value is added to a FLOAT value, the INTEGER value is converted to FLOAT, the data type of the result.

Result Data Type

The following table shows the result data type for binary arithmetic operators.

The result data type for binary arithmetic operations involving UDT operands is the same as the result data type for the predefined data types to which the UDTs are implicitly cast.

For details on the result data type for binary arithmetic operations involving DateTime and Interval types, see “Arithmetic Operators and Result Types” on page 842.

 

When the operand on the left is …

And the operand on the right is …

And the operator is …

Then the result data type is …

any data type

any data type

**

FLOAT

DATE

BYTEINT
SMALLINT
INTEGER
BIGINT

+ -

DATE

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

BYTEINT
SMALLINT
INTEGER

* / MOD

INTEGER

These operations on DATE do not report an error, but results are generally not meaningful.

DATE
(
continued)

BIGINT

* / MOD

BIGINT

These operations on DATE do not report an error, but results are generally not meaningful.

DECIMAL(k,j)

+ -

DATE

These operations on DATE do not report an error, but results are generally not meaningful.

Fractions of decimal values are truncated when added to or subtracted from date values.

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

* / MOD

DECIMAL(p,j)

These operations on DATE do not report an error, but results are generally not meaningful.

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

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

+ -

DATE

These operations on DATE do not report an error, but results are generally not meaningful.

Fractions of decimal values are truncated when added to or subtracted from date values.

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

* / MOD

NUMBER

FLOAT

* / + - MOD

FLOAT

DATE

-

INTEGER

The difference between two dates is the number of days between those dates. Note that this is not the numeric difference between the values.

+ * / MOD

INTEGER

These operations on DATE do not report an error, but results are generally not meaningful.

CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

These operations on DATE do not report an error, but results are generally not meaningful.

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

BYTEINT
SMALLINT
INTEGER

BYTEINT
SMALLINT
INTEGER

* / + - MOD

INTEGER

BIGINT

* / + - MOD

BIGINT

DECIMAL(k,j)

* / + - MOD

DECIMAL(p,j)

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

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

* / + - MOD

NUMBER

FLOAT

* / + - MOD

FLOAT

CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DATE

+

DATE

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

-

error

* / MOD

INTEGER

These operations on DATE do not report an error, but results are generally not meaningful.

BIGINT

BYTEINT
SMALLINT
INTEGER
BIGINT

* / + - MOD

BIGINT

DECIMAL(k,j)

* / + - MOD

DECIMAL(p,j)

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

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

* / + - MOD

NUMBER

FLOAT

* / + - MOD

FLOAT

CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DATE

+

DATE

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

-

error

* / MOD

BIGINT

These operations on DATE do not report an error, but results are generally not meaningful.

DECIMAL(m,n)

 

BYTEINT
SMALLINT
INTEGER
BIGINT

+ - *

DECIMAL(p,n)

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

/ MOD

DECIMAL(m,n)

DECIMAL(k,j)

+ -

DECIMAL(min(p,(1+max(n,j)+max(m-n,k-j))),max(n,j))

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

*

DECIMAL(min(p,m+k),(n+j))

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

/ MOD

DECIMAL(p,max(n,j))

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

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

* / + - MOD

NUMBER

FLOAT

* / + - MOD

FLOAT

CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DATE

+

DATE

Fractions of decimal values are truncated when added to or subtracted from date values.

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

-

error

*

DECIMAL(p,n)

These operations on DATE do not report an error, but results are generally not meaningful.

For details about the value of p, see “DECIMAL Result Data Type” on page 117.

/ MOD

DECIMAL(m,n)

These operations on DATE do not report an error, but results are generally not meaningful.

NUMBER(m,n)
NUMBER(m)
NUMBER(*,n)
NUMBER

BYTEINT
SMALLINT
INTEGER
BIGINT

* / + - MOD

NUMBER

DECIMAL(k,j)

FLOAT

* / + - MOD

 

FLOAT

CHAR(n)
VARCHAR(n)

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DATE

+

DATE

Fractions of decimal values are truncated when added to or subtracted from date values.

If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 9999.

-

error

*

NUMBER

These operations on DATE do not report an error, but results are generally not meaningful.

/ MOD

NUMBER

These operations on DATE do not report an error, but results are generally not meaningful.

 

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

* / + - MOD

NUMBER

FLOAT

BYTEINT
SMALLINT
INTEGER
BIGINT
DECIMAL(
k,j)
NUMBER(k,j)
NUMBER(k)
NUMBER(*,j)
NUMBER
FLOAT

* / + - MOD

FLOAT

DATE

* / + - MOD

FLOAT

These operations on DATE do not report an error, but results are generally not meaningful.

CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

CHAR(n)
VARCHAR(n)

 

BYTEINT
SMALLINT
INTEGER
BIGINT
DECIMAL(
k,j)
NUMBER(k,j)
NUMBER(k)
NUMBER(*,j)
NUMBER
FLOAT
CHAR(n)
VARCHAR(n)

* / + - MOD

FLOAT

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DATE

* / + - MOD

FLOAT

These operations on DATE do not report an error, but results are generally not meaningful.

If an argument of an arithmetic operator is a character string, the first action is to attempt to convert the character string to a floating value. If this conversion fails, an error is reported.

DECIMAL Result Data Type

The result data type for binary arithmetic operations involving DECIMAL operands is as follows:

 

When the operand on the left is …

And the operand on the right is …

And the operator is …

Then the result data type is …

DATE

DECIMAL(k,j)

 

* / MOD

DECIMAL(p,j)

These operations on DATE do not report an error, but results are generally not meaningful.

BYTEINT
SMALLINT
INTEGER
BIGINT

* / + - MOD

DECIMAL(p,j)

DECIMAL(m,n)

 

BYTEINT
SMALLINT
INTEGER
BIGINT

+ - *

DECIMAL(p,n)

DATE

*

DECIMAL(p,n)

These operations on DATE do not report an error, but results are generally not meaningful.

In these cases the value of p, the number of digits in the decimal result, depends on:

  • The value specified for MaxDecimal in DBSControl.
  • For more information on DBSControl and MaxDecimal, see “DBS Control utility” in Utilities: Volume 1 (A-K).

  • The number of digits in the decimal operand, where the number of digits is k for a DECIMAL(k,j) operand on the right side of the operator or m for a DECIMAL(m,n) operand on the left side of the operator.
  •  

    IF MaxDecimal is …

    AND the number of digits in the decimal operand is …

    THEN p is …

    0 or 15

     

     

    <= 15

    15

    > 15 and <=18

    18

    > 18

    38

    18

     

    <= 18

    18

    > 18

    38

    38

    any value

    38

     

    When the operand on the left is …

    And the operand on the right is …

    And the operator is …

    Then the result data type is …

    DECIMAL(m,n)

     

     

    DECIMAL(k,j)

     

     

    + -

    DECIMAL(min(p,(1+max(n,j)+max(m-n,k-j))),max(n,j))

    *

    DECIMAL(min(p,m+k),(n+j))

    / MOD

    DECIMAL(p,max(n,j))

    In these cases, the value of p in the definition of the decimal result data type depends on the value specified for MaxDecimal in DBSControl and the number of digits in the DECIMAL(m,n) and DECIMAL(k,j) operands.

     

    IF MaxDecimal is …

    AND …

    THEN p is …

    0 or 15

     

     

    m and k <= 15

    15

    (m or k > 15) and (m and k <=18)

    18

    m or k > 18

    38

    18

     

    m and k <= 18

    18

    m or k > 18

    38

    38

    m and k = any value

    38

    Numeric Results and Rounding

    When computing an expression, numeric results that are not exact are rounded, not truncated.

    For more information on rounding rules and how the RoundHalfwayMagUp and RoundNumberAsDec fields in DBSControl affect rounding, see “Numeric Data Types” in SQL Data Types and Literals and “DBS Control utility” in Utilities: Volume 1 (A-K).

    Error Conditions

    An error is reported when any of the following events occurs:

  • Division by zero is attempted.
  • The numeric range is exceeded.
  • The exponentiation operator is used with a negative left argument and a right argument that is not a whole number.
  • Integer Division and Truncation

    Integer division yields whole results, truncated toward zero.