# 15.00 - Binary Arithmetic Result Data Types - Teradata Database

## Teradata Database SQL Functions, Operators, Expressions, and Predicates

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

## 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 BYTEINTSMALLINTINTEGERBIGINT + - 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. BYTEINTSMALLINTINTEGER * / 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. BYTEINTSMALLINTINTEGER BYTEINTSMALLINTINTEGER * / + - 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 BYTEINTSMALLINTINTEGERBIGINT * / + - 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) BYTEINTSMALLINTINTEGERBIGINT + - * 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 BYTEINTSMALLINTINTEGERBIGINT * / + - 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 BYTEINTSMALLINTINTEGERBIGINTDECIMAL(k,j)NUMBER(k,j)NUMBER(k)NUMBER(*,j)NUMBERFLOAT * / + - 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) BYTEINTSMALLINTINTEGERBIGINTDECIMAL(k,j)NUMBER(k,j)NUMBER(k)NUMBER(*,j)NUMBERFLOATCHAR(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. BYTEINTSMALLINTINTEGERBIGINT * / + - MOD DECIMAL(p,j) DECIMAL(m,n) BYTEINTSMALLINTINTEGERBIGINT + - * 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.