DATETIME ADDITION = OPERAND + OPERAND
A duration is a value that represents an interval of time. The value may be a constant, a column name, a host variable, a function, an expression, or an expression followed by a duration attribute. Numbers are interpreted as durations only in certain contexts as defined in the DB2 Server for VSE & VM SQL Reference manual; the arithmetic of using date, time, and timestamp is discussed in detail. Figure 87 and Figure 88 summarize this topic.
What does it mean to add a month to a given date? Presumably the result should be the same day of the next month. That is, one month after January 1 is February 1, and one month after February 1 is March 1. But what is one month after January 31? This difficulty (which is the reason why certain contracts are always dated the first of the month) is resolved by the further assumption that the result should be the last day of February. Thus, adding a month to a given date gives the same day of the next month except when the next month does not have such a day, in which case the result is the last day of that month. But, one month from the last day of a month is not necessarily the last day of the next month. One month from the last day of February, for example, is not the last day of March. Thus (a date) + (a simple duration of months) - (a simple-duration of months) is not necessarily equal to the original date.
The definition of a month does not permit a consistent system of date
arithmetic. If this is a problem, it can be avoided by using days
rather than months. For example, to increment the date date3
by the difference between the dates date1 and date2, the
expression:
DATE ( DAYS(date1) - DAYS(date2) + DAYS(date3) )
will give an accurate result whereas date1 - date2 + date3 may not. Figure 86 shows how SQLWARN7 provides warnings during date arithmetic when the resulting date has to be adjusted to derive a valid date.
Figure 86. Setting SQLWARN7 during Date Arithmetic. When incrementing or decrementing dates, SQLWARN7 is set when the resulting date is an invalid date because of a leap year or month difference, and a valid date is derived.
Let D1 be the DATE 2000-02-29, a leap year:
SQLWARN7
D1 + 1 DAY = 2000-03-01 ' '
D1 + 2 MONTHS = 2000-04-29 ' '
D1 + 1 YEAR = 2001-02-28 'W'
D1 + 4 YEARS = 2004-02-29 ' '
Let N be DEC(8,0) and set to 00010203.
D1 + N
= 2000-02-29 + 1 YEAR + 2 MONTHS + 3 DAYS
= 2001-02-28 + 2 MONTHS + 3 DAYS 'W'
= 2001-04-28 + 3 DAYS
= 2001-05-01
Let D2 be the DATE 2001-03-31:
SQLWARN7
D2 + 1 MONTH = 2001-04-30 'W'
D2 + 2 MONTHS = 2001-05-31 ' '
|
Figure 87. Datetime Addition
DATETIME ADDITION = OPERAND + OPERAND
Figure 88. Datetime Subtraction
DATETIME SUBTRACTION = MINUEND - SUBTRAHEND
