Description of to_yminterval.gif follows
Description of the illustration to_yminterval.gif


Description of ym_iso_format.gif follows
Description of the illustration ym_iso_format.gif


TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type.

TO_YMINTERVAL accepts argument in one of the two formats:

  • SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)

  • ISO duration format compatible with the ISO 8601:2004 standard

In the SQL format, years is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements.

In the ISO format, years and months are integers between 0 and 999999999. Days, hours, minutes, seconds, and frac_secs are non-negative integers, and are ignored, if specified. No blanks are allowed in the value.


The following example calculates for each employee in the sample hr.employees table a date one year two months after the hire date:

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
   FROM employees;

HIRE_DATE 14 months
--------- ---------
17-JUN-87 17-AUG-88
21-SEP-89 21-NOV-90
13-JAN-93 13-MAR-94
03-JAN-90 03-MAR-91
21-MAY-91 21-JUL-92
. . .