Also, the system function DateTimeLib.extend returns a timestamp value that is longer or shorter than an input field of type DATE, TIME, or TIMESTAMP.
The next table summarizes the types of arithmetic operations that are valid in a datetime expression. As shown, a datetime expression may include a numeric expression that returns a number, but only in a subset of cases.
Type of Operand 1 | Operator | Type of Operand 2 | Type of Result | Comments |
---|---|---|---|---|
DATE | - | DATE | INT | |
DATE | +/- | NUMBER | DATE | |
NUMBER | + | DATE | DATE | |
TIME STAMP | - | TIMESTAMP | INTERVAL | INTERVAL(dd, ss) unless Operand 1 and Operand 2 are
both any of the following:
In those three cases, the result is INTERVAL(yyyyMM) |
DATE | - | TIMESTAMP | INTERVAL | INTERVAL(ddssmmffffff) |
TIME STAMP | - | DATE | INTERVAL | INTERVAL(ddHHmmssffffff) |
TIME STAMP | +/- | INTERVAL | TIMESTAMP | |
INTERVAL | + | TIMESTAMP | TIMESTAMP | |
DATE | +/- | INTERVAL | TIMESTAMP | |
INTERVAL | + | DATE | TIMESTAMP | |
INTERVAL | +/- | INTERVAL | INTERVAL | Operand1 and Operand2 must both have (at most) years and months or both must have (at most) days and a time value |
INTERVAL | *// | NUMBER | INTERVAL |