Add
The add operator (+) adds two operands and returns the sum.
Note: A configuration parameter is available to control whether this operator allows precision/scale to exceed 38. See
Decimal Digit Limitation on Functions, for details.
DECIMAL and NUMERIC Data Types
When the add operator is applied to operands that include DECIMAL or NUMERIC data types, the output data type, precision and scale might depend on the data type, precision and scale of the operands, as shown below.
Syntax
operand1 + operand2
Remarks
• The order of the inputs (operands) has no effect on the output data type.
• The outputs for DECIMAL and NUMERIC data types combined with other operands are shown in the table.
Inputs | Output |
DECIMAL(p1,s1) + DECIMAL(p2,s2) | DECIMAL(p3,s3), with p3 the larger precision of the inputs plus 1, and s3 the larger scale of the inputs. |
DECIMAL(p1,s1) + NUMERIC |
NUMERIC + NUMERIC | NUMERIC |
DECIMAL(p,s) + not-DECIMAL-or-NUMERIC | DECIMAL(p,s) |
NUMERIC + not-DECIMAL-or-NUMERIC | NUMERIC |
Example
DECIMAL(6,1) + NUMERIC(4,2) -> DECIMAL(7,2)
INTERVAL Type
INTERVAL can be added to DATE, TIME, TIMESTAMP or another INTERVAL.
Syntax
operand1 + operand2
Remarks
• INTERVAL days, hours, minutes, or seconds can only be added to other INTERVAL days, hours, minutes, or seconds. INTERVAL years or months can only be added to other INTERVAL years or months. The two groups of units are not interchangeable.
• When adding months, the TDV Server does not round down the day of the month, and it might throw an error if the day of the month is invalid for the specified month.
• The order of the inputs (operands) has no effect on the output data type.
• The outputs for INTERVAL added to various operands are shown in the table.
Inputs | Output |
INTERVAL + INTERVAL | INTERVAL |
INTERVAL + DATE
DATE + INTERVAL | DATE. Only days, months, and years can be added to a DATE. |
INTERVAL + TIME
TIME + INTERVAL | TIME |
INTERVAL + TIMESTAMP
TIMESTAMP + INTERVAL | TIMESTAMP |
Examples
DATE '1999-12-31' + INTERVAL '1' DAY = DATE '2000-01-01'
INTERVAL '1' MONTH + DATE '1999-12-31'= DATE '2000-01-31'
DATE '1989-03-15' + INTERVAL '1' YEAR = DATE '1990-03-15'
DATE '2000-01-31' + INTERVAL '1' MONTH = <Error: February only has 28 days>
INTERVAL '6000' SECOND(4) + INTERVAL '3000' DAY(4) = INTERVAL '3000 01:40:00' DAY(4) TO SECOND
INTERVAL '6000' SECOND(4) + TIME '7:00:00' = TIME '08:40:00'
Mixed Data Types
The add operator can be applied to operands that have a wide variety of data types, including operands comparable or castable to data types that can accept arithmetic operators.
Syntax
operand1 + operand2
Remarks
The operand data types and resulting output data types are shown in the table.
Operand1 Type | Operand2 Type | Output Type |
TINYINT
SMALLINT
INTEGER
BIGINT | TINYINT
SMALLINT
INTEGER
BIGINT
STRING | INTEGER |
TINYINT
SMALLINT
INTEGER
BIGINT | FLOAT
REAL | FLOAT |
TINYINT
SMALLINT
INTEGER
BIGINT | DECIMAL
NUMERIC | DECIMAL |
TINYINT
SMALLINT
INTEGER
BIGINT
STRING | DATE | DATE |
TINYINT
SMALLINT
INTEGER
BIGINT
STRING | TIMESTAMP | TIMESTAMP |
FLOAT
REAL | TINYINT
SMALLINT
INTEGER
BIGINT
STRING | FLOAT |
FLOAT
REAL | FLOAT
REAL |
FLOAT
REAL | DECIMAL
NUMERIC | DECIMAL |
FLOAT
REAL | DATE | DATE |
FLOAT
REAL | TIMESTAMP | TIMESTAMP |
DECIMAL
NUMERIC | TINYINT
SMALLINT
INTEGER
BIGINT | DECIMAL |
DECIMAL
NUMERIC | FLOAT
REAL |
DECIMAL
NUMERIC | DECIMAL
NUMERIC | FLOAT |
DECIMAL
NUMERIC | DATE | DATE |
DECIMAL
NUMERIC | TIMESTAMP | TIMESTAMP |
DATE | INTERVAL | DATE |
DATE | STRING | DATE |
TIMESTAMP | INTERVAL | TIMESTAMP |
TIMESTAMP | STRING | TIMESTAMP |
STRING | STRING
TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP | DECIMAL
INTEGER
INTEGER
INTEGER
INTEGER
DECIMAL
FLOAT
FLOAT
DECIMAL
DATE
TIMESTAMP |
NULL | TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP
NULL | NULL |
TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP
STRING | NULL | NULL |
INTERVAL | DATE
INTERVAL
TIME
TIMESTAMP | DATE
INTERVAL
TIME
TIMESTAMP |