TO_YMINTERVAL function

The TO_YMINTERVAL function converts a string representing a time unit to an INTERVAL YEAR TO MONTH literal value. This function can also accept a number and a string as its arguments, and return an INTERVAL value with a single time-unit precision of YEAR or MONTH.

You can use the TO_YMINTERVALfunction with a single argument (or with two arguments, its synonym, NUMTOYMINTERVAL ) to specify an interval range value when you are defining a range-interval storage distribution strategy to fragment a table or an index.

Syntax

Numeric to INTERVAL

|--+-TO_YMINTERVAL---+--(--number--,--+-'--YEAR--'--+--)--------|
   '-NUMTOYMINTERVAL-'                '-'--MONTH--'-'      

String to INTERVAL

|--TO_YMINTERVAL--(--' YY -MM '--)------------------------------|
Element Description Restrictions Syntax
number The number of years or months in the interval.

This can be an expression, including a column expression, that resolves (or can be cast) to one of the valid data types.

 Must be one of the following data types:
  • INT
  • BIGINT
  • SMALLINT
  • INT8
  • DECIMAL
  • REAL
  • FLOAT
  • SERIAL
  • SERIAL8
  • BIGSERIAL
 Literal number
MM Two digits specifying the number of months in the interval. A hyphen ( - ) must precede the first digit. Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string
YY Two digits specifying the number of years in the interval. Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string

Usage

You can use the TO_YMINTERVAL function to specify an interval value when you fragment a table or index by an interval. The TO_YMINTERVAL function is valid in any context that a built-in routine is allowed. The NUMTOYMINTERVAL function is a synonym to the TO_YMINTERVAL function for converting numeric values.

Examples

The following examples show how different values for the TO_YMINTERVAL function are interpreted.

The following examples specify an interval of one year:

TO_YMINTERVAL('01-00')
TO_YMINTERVAL(1,'YEAR')
NUMTOYMINTERVAL(1,'YEAR')

The following examples specify an interval of one month:

TO_YMINTERVAL('00-01')
TO_YMINTERVAL(1,'MONTH')
NUMTOYMINTERVAL(1,'MONTH')

The following examples specify an interval of one year and six months:

TO_YMINTERVAL('01-06')
TO_YMINTERVAL(1.5,'YEAR')
NUMTOYMINTERVAL(1.5,'YEAR')

The following example shows how to use an expression as a numeric value:

TO_YMINTERVAL(10+10+100,'YEAR')

The following example defines table t2 with a range interval fragmentation scheme. Here DATETIME column dt1 is the fragmentation key, and the return value from NUMTOYMINTERVAL defines the interval size as 25 years. Rows with dt1 values for years later than 2005 but earlier than 2031 will be stored in the range fragment p1:

CREATE TABLE t2 (c1 int, d1 date, dt1 DATETIME YEAR TO FRACTION)
   FRAGMENT BY RANGE (dt1) INTERVAL (NUMTOYMINTERVAL (25,'YEAR'))
      PARTITION p1 VALUES <
         DATETIME(2006-01-01 00:00:00.00000) YEAR TO FRACTION(5) IN dbs1;

If a row is inserted in which the YEAR value in dt1 is less than 2006 or greater than 2030, the database server will automatically create a new interval fragment, where the size of its range is 25 years. For more information about the syntax and semantics of range interval fragmentation, see Interval fragment clause.