2.11 Date operations

Organization of this section
(1) Function
(2) Data eligible for operations
(3) Labeled duration
(4) Format
(5) Explanation
(6) Format of a date operation and the data type of the result
(7) Rules for date operations
(8) Rules for addition and subtraction of date data and date interval data
(9) Rules for addition or subtraction between date interval data, and multiplication or division of date interval data by an integer
(10) Notes

(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

OperationApplicable date operation values
Date dataDate 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, DivisionNot specifiableInteger data (INTEGER, SMALLINT)
Unary operationNot 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

  1. 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
  2. The value expression must have an integer data type (SMALLINT or INTEGER).
  3. 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
  4. 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 formatData type of result
date-data - date-dataDate interval data type
date-data {+|-} {date-interval-data|labeled-duration}Date data type
date-interval-data + date-dataDate data type
date-interval-data {+|-} date-interval-dataDate interval data type
date-interval-data {*|/} integer-dataDate 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
date1[Figure] date2:
Result = date1 - date2
date1 < date2:
Result = - (date2 - date1)#
Day of date1[Figure] 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[Figure] 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]
(b) Explanation of the algorithm

[Figure]

(8) Rules for addition and subtraction of date data and date interval data

  1. The result of addition or subtraction of date data or date interval data is the date data type.
  2. The allowable range of the result of a computation is from 1/1/0001 through 12/31/9999.
  3. Date interval data (not a labeled duration) is computed in the order of year, month, and day.
  4. 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.
  5. 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[Figure] DATE('1995-02-28')
DATE('1995-02-28') - 1 MONTH[Figure] 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

  1. The results of these computations take the date interval data type.
  2. 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.
  3. 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:

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.