The only arithmetic operations that can be performed on date/time values are addition and subtraction.
Date/time Addition
If a date/time value is the operand of addition, the other operand must be a duration. These rules govern the use of the addition operator with date/time values:
Date/time Subtraction
The rules for the use of the subtraction operator on date/time values differ from those for addition. The tables below describe the rules for using the subtraction operator with date/time values.
Note: The second operand cannot be a timestamp.
First Operand Rules
|
If the first operand is |
The second operand must be |
|---|---|
|
A date |
|
|
A time |
|
|
A timestamp |
A duration |
Second Operand Rules
|
If the second operand is |
The first operand must be |
|---|---|
|
A date |
|
|
A time |
|
Dates can be subtracted, incremented, or decremented.
Subtracting Dates
The result of subtracting one date from another date is a date duration in the form yyyymmdd that specifies the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0).
In the expression D1 - D2, where D1 and D2 are date values:
If D1 is greater than or equal to D2, D2 is subtracted from D1 If D1 is less than D2, D1 is subtracted from D2, and the sign of the result is made negative
Date Subtraction Procedures
These are the procedures used to obtain a result R in the expression R = D1 - D2 where D1 and D2 are date values:
If DAY(D2) < = DAY(D1), then DAY(R) = DAY(D1) - DAY(D2). If DAY(D2) > DAY(D1), then DAY(R) = N + DAY(D1) - DAY(D2) where N = the last day of MONTH(D2). MONTH(D2) is then incremented by 1. If MONTH(D2) < = MONTH(D1), then MONTH(R) = MONTH(D1) - MONTH(D2). If MONTH(D2) > MONTH(D1), then MONTH(R) = 12 + MONTH(D1) - MONTH(D2). YEAR(D2) is then incremented by 1. YEAR(R) = YEAR(D1) - YEAR(D2).
Example of Subtracting Dates
The result of DATE('12/31/2000') - DATE('8/10/1999') is 10421, representing a duration of 1 year, 4 months, and 21 days.
What You Can Do
The result of adding a duration to a date or subtracting a duration from a date is a date. The result must fall in the range of dates from January 1, 0001 to December 31, 9999.
:warning. If an invalid date is calculated during an UPDATE STATEMENT, the target column remains unchanged. An invalid date can be calculated during "Operations with a Duration of Years" or "Operations with a Duration or Months."
Operations with a Duration of Years
Adding or subtracting a duration of years affects the year of the resulting date but does not affect the month or day unless the result is February 29 of a non-leap year. In this case, the day portion is set to 28. When this adjustment is required, a warning message is issued.
For example, the result of DATE('5/1/1998') + 3 YEARS is '5/1/2001'.
Operations with a Duration of Months
Adding or subtracting a duration of months affects the month and potentially the year of the resulting date. The day portion of the date is unchanged unless the result is an invalid date, such as June 31. When an invalid date is calculated, CA IDMS returns a warning message. If the invalid date is calculated during a SELECT statement, the date is set to the last day of the month.
For example, the result of DATE ('10/31/2001') - 1 MONTH is '9/30/2001'.
Operations with a Duration of Days
Adding or subtracting a duration of days affects the day of the resulting date and potentially the month and year.
For example, the result of DATE ('12/15/2000') + 45 DAYS is '1/29/2001'.
Operations with Date Durations
Date durations of data type DECIMAL (8,0) in the form yyyymmdd may also be added to and subtracted from dates. The date duration may be a positive or negative value.
The result is a date that has been incremented or decremented by the specified number of years, months, and days, respectively. Thus, D1 + N, where N is a positive date duration, is equivalent to this expression:
D1 + YEAR(N) YEARS + MONTH(N) MONTHS + DAY(N) DAYS
For example, the result of DATE('4/13/2001') + 101 is '5/14/2001'.
Note: Leading zeros are dropped. Therefore, 101 is the same as 00000101.
Reversing Operations with Date Durations
If you add duration 100 (one month) to date D1, obtaining result R, R - 100 may not necessarily equal D1 because the operation D1 + 100 may require an end-of-the-month adjustment. For example:
DATE('8/31/2001') + 100 = '9/30/2001'
However:
DATE('9/30/2001') - 100 = '8/30/2001'
Times can be subtracted, incremented, or decremented.
Subtracting Times
The result of subtracting one time (T2) from another (T1) is a time duration in the form hhmmss that specifies the number of hours, minutes, and seconds between the two times. The data type of the result is DECIMAL (6,0).
In the expression T1 - T2, where T1 and T2 are time values:
If T1 is greater than or equal to T2, T2 is subtracted from T1 If T1 is less than T2, T1 is subtracted from T2, and the sign of the result is made negative
Time Subtraction Procedures
These are the procedures used to obtain a result R in the expression R = T1 - T2 where T1 and T2 are time values:
SECOND(T2) < = SECOND(T1), then SECOND(R) = SECOND(T1) - SECOND(T2). If SECOND(T2) > SECOND(T1), then SECOND(R) = 60 + SECOND(T1) - SECOND(T2). MINUTE(D2) is then incremented by 1. If MINUTE(T2) < = MINUTE(T1), then MINUTE(R) = MINUTE(T1) - MINUTE(T2). If MINUTE(T2) > MINUTE(T1), then MINUTE(R) = 60 + MINUTE(T1) - MINUTE(T2). HOUR(T2) is then incremented by 1. HOUR(R) = HOUR(T1) - HOUR(T2).
For example, the result of TIME ('16:43:17') - TIME('14:30:00') is 21317, representing a duration of 2 hours, 13 minutes and 17 seconds.
What is the Result?
The result of adding a duration to a time, or of subtracting a duration from a time, is a time.
Operations With a Duration of Hours
Adding or subtracting a duration of hours affects the hours of the resulting time. The minutes and seconds are unchanged.
For example, the result of TIME ('16:43:17') + 3 HOURS is '19:43:17'.
Operations With a Duration of Minutes
Adding or subtracting a duration of minutes affects the minutes and potentially the hours of the resulting time. The second's portion of the time is unchanged.
For example, the result of TIME ('16:43:17') + 30 MINUTES is '17:13:17'.
Operations With a Duration of Seconds
Adding or subtracting a duration of seconds affects the seconds and potentially the minutes and hours of the resulting time.
For example, the result of TIME ('16:43:17') + 51 SECONDS is '16:44:08'.
Note: In arithmetic with a time and a duration, overflow or underflow ofhours is discarded.
Operations With Time Durations
Time durations of a data type DECIMAL(6,0) may also be added to and subtracted from times. The time duration may be a positive or negative value.
The result is a time that has been incremented or decremented by the specified number of hours, minutes, and seconds, respectively. Thus, T1 + N, where N is a positive time duration, is equivalent to this expression:
T1 + HOUR(N) HOURS + MINUTE(N) MINUTES + SECONDS(N) SECONDS
For example, the result of TIME ('16:43:17') + 32114 is '20:08:31'.
Timestamps can be incremented, or decremented. The result of adding a duration to a timestamp, or of subtracting a duration from a timestamp, is a timestamp.
Timestamp arithmetic is performed as described for date and time arithmetic except that an overflow or underflow of hours is carried into the date part of the result.
Expressions within parentheses are evaluated first. When the order of evaluation is not specified by parentheses:
|
Copyright © 2014 CA.
All rights reserved.
|
|