The only arithmetic operations that can be performed on datetime values are addition and subtraction.
Addition operator
If a datetime value is the operand of addition, the other operand must be a labeled duration.
The specific HCOSS rules governing the use of the addition operator with datetime values are:
| First operand type
|
Other operand must be
|
| date
|
A labeled date duration of years, months, or days.
|
| time
|
A labeled time duration of hours, minutes, or seconds.
|
| timestamp
|
A labeled date, time or timestamp duration. Any type of labeled duration is valid.
|
The result of HCOSS datetime addition is always a date, time or timestamp.
Note: Date, time or timestamp durations cannot be used to increment date, time or timestamp columns. Instead, use corresponding
labeled durations as cited above.
Subtraction operator
The HCOSS rules for the use of the subtraction operator on datetime values are not the same as those for addition because
a datetime value cannot be subtracted from a duration, and because the operation of subtracting two datetime values is not
the same as the operation of subtracting a duration from a datetime value.
The general rules governing the use of the subtraction operator with datetime values are:
| Operand1 - Operand2
|
Result
|
| date - date
|
date duration - decimal(8.0) formatted 'yyyymmdd'
|
| date - labeled date duration
|
date
|
| time - time
|
time duration - decimal(6.0) formatted 'hhmmss'
|
| time - labeled time duration
|
time
|
| timestamp - timestamp
|
timestamp duration - decimal(20,6) formatted 'yyyymmddhhmmss.nnnnnn'
|
| timestamp - any labeled duration
|
timestamp
|
Specific additional HCOSS date subtraction rules:
- If the first operand is a date, the second operand must be a labeled duration of years, months, days or a DATE(date or string
representation of a date) expression.
- If the second operand is a date, the first operand must be a DATE(date or string representation of a date) expression.
- If either operand is CURRENT_DATE, DATE function is not required for other operand.
Specific additional HCOSS time subtraction rules:
- If the first operand is a time, the second operand must be a labeled duration of hours, minutes, seconds or a TIME(time or
string representation of a time) expression.
- If the second operand is a time, the first operand must be a TIME(time or string representation of a time) expression.
- If either operand is CURRENT_TIME, TIME function is not required for other operand.
Specific additional HCOSS timestamp subtraction rules:
- If the first operand is a timestamp, the second operand must be any valid labeled duration or a TIMESTAMP(timestamp or string
representation of a timestamp) expression.
- If the second operand is a timestamp, the first operand must be a TIMESTAMP(timestamp or string representation of a timestamp)
expression.
- If either operand is CURRENT_TIMESTAMP, TIMESTAMP function is not required for other operand.
- The precision of TIMESTAMP is set to be the same as SQL Server's datetime2(6) data type.
Note: Date, time or timestamp durations cannot be used to decrement date, time or timestamp columns. Instead, use corresponding
labeled durations. HCOSS does support one special case, however - the expression (CURRENT_TIMESTAMP - CURRENT_TIMEZONE).
Examples
| Valid z/OS DB2 expressions
|
Required HCOSS expressions
|
| COL_TS - COL_TS
|
COL_TS - TIMESTAMP(COL_TS)
|
| COL_DT - '1900-01-01'
|
COL_DT - DATE('1900-01-01')
|
| '12.12.12' - COL_TM
|
TIME('12.12.12') - TIME(COL_TM)
|
| Acceptable HCOSS expressions
|
| CURRENT_TIMESTAMP - COL_TS
|
| CURRENT_DATE - '1900-01-01'
|
| COL_TM - CURRENT_TIME
|
| CURRENT_TIMESTAMP - CURRENT_TIMEZONE
|