yearMonthDuration data type

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

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

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

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 forms P24YM and PY43M are not allowed because Y must have at least one preceding digit and M must have one preceding digit.

The Db2 database system stores xdt:yearMonthDuration values in a normalized form. In the normalized form, the months component is less than 12. Each multiple of 12 months is converted to one year. For example, the following XQuery expression invokes a constructor function specifying a yearMonthDuration of 20 years and 30 months:

xquery
xdt:yearMonthDuration("P20Y30M")

In the duration, 30 months is converted to 2 years and 6 months. The expression returns the normalized yearMonthDuration value P22Y6M.