Oracle TO_YMINTERVAL function

Description

The TO_YMINTERVAL() function is used to converts a character string ( 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). In this format, year is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements.
• ISO duration format compatible with the ISO 8601:2004 standard. In this 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.

Syntax:

TO_YMINTERVAL
  ( '  { [+|-] years - months 
       | ym_iso_format 
       } ' )

ym_iso_format:

[-] P [ years Y ] [months M] [days D]
  [T [hours H] [minutes M] [seconds [. frac_secs] S ] ]

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example: Oracle TO_YMINTERVAL function

The following SQL query calculates for each employee a date one year three months after the hire date :

Sample table : employees

SQL> SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "15 months"
 2     FROM employees;

 

Sample Output:

HIRE_DATE 14 months
--------- ---------
HIRE_DATE 15 months
--------- ---------
17-JUN-03 17-AUG-04
21-SEP-05 21-NOV-06
13-JAN-01 13-MAR-02
03-JAN-06 03-MAR-07
21-MAY-07 21-JUL-08
25-JUN-05 25-AUG-06
05-FEB-06 05-APR-07
07-FEB-07 07-APR-08
17-AUG-02 17-OCT-03
16-AUG-02 16-OCT-03
28-SEP-05 28-NOV-06
. . .

Previous: NUMTODSINTERVAL
Next: ROUND(date)