Oracle Date/Time Function - Oracle/PLSQL TO_YMINTERVAL Function






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.

Syntax

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.





Example


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>




Convert Table Data


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>