Addition (+) and subtraction (-) operators

Addition (+) operator returns the algebraic sum of its operands.

Subtraction (-) operator returns the algebraic difference between its left and right operands.

 

Numeric values are added and subtracted according to basic rules of mathematics.

 

When you want to add or subtract time values, you must remember these rules:

1. You cannot add or subtract two DATE values but you can have a DATE value as one of the operands.

2. You cannot add two DATETIME values but you can subtract them.

3. You cannot subtract DATE or DATETIME values from an INTERVAL value.

4. Difference between a DATETIME and a DATE value or two DATETIME values is an INTERVAL value. If the operands have different qualifiers, the result has the qualifier of the first operand.

   

   

5. Difference between two DATE values is an INTEGER value, representing the positive or negative number of days between the two calendar dates. If you want to store this difference as an INTERVAL DAY TO DAY value, you must explicitly apply the UNITS DAY operator to it.

 

Arithmetic operations with DATE or DATETIME values can produce errors. 

For example, if you try to add or subtract a UNITS MONTH operand to/from a date near the end of a month, you might get a compilation error - -1206. Invalid day in date.