This Oracle tutorial explains how to use the Oracle/PLSQL TO_YMINTERVAL function.
The Oracle/PLSQL TO_YMINTERVAL function converts a string to an INTERVAL YEAR TO MONTH type.
TO_YMInterval(x) converts the string x to an INTERVAL YEAR TO MONTH.
x can be a char, varchar2, nchar, or nvarchar2 value.
The syntax for the Oracle/PLSQL TO_YMINTERVAL function is:
TO_YMINTERVAL( character )
character is the value to convert to an INTERVAL YEAR TO MONTH type. It can be a char, varchar2, nchar, or nvarchar2 value.
SQL> select to_yminterval('00-11') from dual;
-- ww w. j a va2 s .c o m
TO_YMINTERVAL('00-11')
--------------------------------------------------------
+000000000-11
SQL> select to_yminterval('01-10') from dual;
TO_YMINTERVAL('01-10')
--------------------------------------------------------
+000000001-10
SQL> select to_yminterval('02-05') from dual;
TO_YMINTERVAL('02-05')
--------------------------------------------------------
+000000002-05
SQL> select to_yminterval('03-01') from dual;
TO_YMINTERVAL('03-01')
--------------------------------------------------------
+000000003-01
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
HIREDATE DATE);
-- ww w . ja va 2 s . c o m
INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> select hiredate, hiredate + to_yminterval('01-05')
2 from emp;
HIREDATE HIREDATE+
--------- ---------
17-DEC-80 17-MAY-82
20-FEB-81 20-JUL-82
22-FEB-81 22-JUL-82
02-APR-81 02-SEP-82
28-SEP-81 28-FEB-83
SQL>