xs:yearMonthDuration

The data type xs:yearMonthDuration represents a duration of time that is expressed by the Gregorian year and month components. xs:yearMonthDuration is derived from data type xs:duration.

The range that can be represented by this data type is from -P83333333333333Y3M to P83333333333333Y3M (or -999999999999999 to 999999999999999 months).

The lexical form of xs:yearMonthDuration is PnYnM, which is a reduced form of the ISO 8601 format. The following abbreviations describe this form:

P
The duration designator.
nY
n is an unsigned integer that represents the number of years.
nM
n is an unsigned integer that represents the number of months.

An optional preceding minus sign (-) indicates a negative duration. If the sign is omitted, a positive duration is assumed.

For example, the following form indicates a duration of 1 year and 2 months: 
P1Y2M
The following form indicates a duration of negative 13 months:
-P13M
Reduced precision and truncated representations of this format are allowed, but they must conform to the following requirements:
  • The designator P must always be present.
  • If the number of years or months in any expression equals zero, the number and its corresponding designator can be omitted. However, at least one number and its designator (Y or M) must be present.
For example, the following forms are allowed:
P1347Y
P1347M

The form P-1347M is not allowed, but the form -P1347M is allowed. The form P24YM is not allowed because M must have one preceding digit. PY43M is not allowed because Y must have at least one preceding digit.

Db2 stores xs:yearMonthDuration values in a normalized form. In the normalized form, the months component is less than 12. Db2 converts each multiple of 12 months to one year. For example, the following XQuery expression invokes a constructor function that specifies a yearMonthDuration of 20 years and 30 months:

xs:yearMonthDuration("P20Y30M")

Db2 converts 30 months to 2 years and 6 months. The expression returns the normalized yearMonthDuration value P22Y6M.