2.11 Date operations
(1) Function
Date operations enable retrieval and updating involving dates and date intervals to be performed.
The date function supports the following operations: subtraction on dates; addition and subtraction of the dates between date intervals; addition and subtraction on date intervals; multiplication and division of a date interval by an integer.
(2) Data eligible for operations
The following table lists the date data or date interval data and applicable date operation values.
Table 2-17 Date operation data
Operation | Applicable date operation values |
---|
Date data | Date interval data |
---|
Addition | - Date interval data (INTERVAL YEAR TO DAY)
- Literals that express date intervals as decimal numbers
- Labeled duration (YEAR[S], MONTH[S], DAY[S])
| - Date data (DATE)
- Literals in which a date is specified in a predefined character string representation
- Date interval data (INTERVAL YEAR TO DAY)
- Literals that express date intervals as decimal numbers
|
Subtraction | - Date data (DATE)
- Literals in which a date is specified in a predefined character string representation
- Date interval data (INTERVAL YEAR TO DAY)
- Literals that express date intervals as decimal numbers
- Labeled duration (YEAR[S], MONTH[S], DAY[S])
| - Date interval data (INTERVAL YEAR TO DAY)
- Literals that express date intervals as decimal numbers
|
Multiplication, Division | Not specifiable | Integer data (INTEGER, SMALLINT) |
Unary operation | Not specifiable in date data or as a labeled duration |
(3) Labeled duration
A labeled duration used in date operations expresses specific units of time as numeric values followed by interval keywords. A labeled duration can be specified only in the second operand for addition or subtraction of date interval data relative to date data.
(4) Format
(value-expression){YEAR [S]|MONTH [S]|DAY [S]}
(5) Explanation
- The following items can be specified in a value expression:
- Integer literals
- Column specification
- Component specification
- SQL variable or SQL parameter
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expression
- CAST specification
- Function call
- Scalar subquery
- The value expression must have an integer data type (SMALLINT or INTEGER).
- YEAR [S], MONTH [S], and DAY [S] indicate the units of years, months, and days, respectively. The S suffix is optional.
- Specification examples are given below:
- 1 year: 1 YEAR
- 11 months: 11 MONTHS
- 100 days: 100 DAYS
- The following ranges of values can be specified in a value expression:
YEAR [S]: -9998 to 9998
MONTH [S]: -199987 to 119987
DAY [S]: -3652058 to 3652058
(6) Format of a date operation and the data type of the result
The following table indicates the relationships between the format of a date operation and the data type of the result.
Table 2-18 Relationships between the format of a date operation and the data type of the result
Operation format | Data type of result |
---|
date-data - date-data | Date interval data type |
date-data {+|-} {date-interval-data|labeled-duration} | Date data type |
date-interval-data + date-data | Date data type |
date-interval-data {+|-} date-interval-data | Date interval data type |
date-interval-data {*|/} integer-data | Date interval data type |
Note: Embedded variables, indicator variables, and ? parameters cannot be specified in a date operation involving date data or date interval data.
(7) Rules for date operations
For the rules for performing date operations, see the rules below and the rules provided in Section 2.9 Value expressions, value specifications, and item specifications.
(a) Rules for subtracting one date data item from another
- The data type of the result of subtraction involving two date data items is the date interval data type that expresses the number of years, months, and days.
- The result of the operation expression (date1 - date2) is computed according to the following rules:
- date1
date2: - Result = date1 - date2
- date1 < date2:
- Result = - (date2 - date1)#
- Day of date1
day of date2: - Day of result = day of date1 - day of date2
- Day of date1 < day of date2:
- Day of result = day of date1 - day of date2 + last day of month of date2
- Month of date2 = month of date2 + 1
- Month of date1
month of date2: - Month of result = month of date1 - month of date2
- Month of date1 < month of date2:
- Month of result = month of date1 - month of date2 + 12
- Year of date2 = year of date2 + 1
Year of result = year of date1 - year of date2
#: The result of subtraction will be the result of date2 - date1 with a minus sign.
- Example
- Determine the result of the subtraction DATE('1995-10-15')-DATE('1989-12-16'):
![[Figure]](figure/zu2s0120.gif)
(b) Explanation of the algorithm
![[Figure]](figure/zu2s0130.gif)
(8) Rules for addition and subtraction of date data and date interval data
- The result of addition or subtraction of date data or date interval data is the date data type.
- The allowable range of the result of a computation is from 1/1/0001 through 12/31/9999.
- Date interval data (not a labeled duration) is computed in the order of year, month, and day.
- If the result of an operation on a year and month is a non-existent date (31st day of a 30-day month or February 29 of a non-leap-year), the date is changed to the last day of the month.# When a non-existent date is generated and the resulting date has been so modified, 'W' is set in the SQLWARNA variable.
- If the result of an operation on a year and month is beyond the last date of the month or is before the first day (1st) of the month, the year and month are rounded up or down, as appropriate.
#: Adding of months to the last day of a month does not necessarily produce the last day of the resulting month. Likewise, adding months to a day and subtracting the same number of months from the result does not necessarily produce the original date.
- Example
- DATE('1995-01-31') + 1 MONTH
DATE('1995-02-28') - DATE('1995-02-28') - 1 MONTH
DATE('1995-01-28') - Adding 1 month to 1/31/1995 results in 2/28/1995. However, subtracting 1 month from 2/28/1995 results in 1/28/1995, rather than the original 1/31/1995.
(9) Rules for addition or subtraction between date interval data, and multiplication or division of date interval data by an integer
- The results of these computations take the date interval data type.
- If the result of a date computation falls outside the range 00 to 99, the "W" warning is set to the SQLWARNC area. In this case, 00 is set as the number of days if the result is less than 00, and 99 is set if the result is greater than 99.
- In the result of division, any digits following the decimal point are rounded off.
(10) Notes
The rules below for date operations do not result in an error when the overflow error suppression feature is set:
- Overflow occurs in the date data type
- Overflow occurs in the date interval data type
- Overflow occurs in the labeled duration data type
For details about the operational results produced when the overflow error suppression feature is set, see 2.18 Operational results with overflow error suppression specified.