Purpose
TO_YMINTERVAL() converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type. This function can be used to perform addition and subtraction operations on a datetime value.
Syntax
/*SQL date format*/
TO_YMINTERVAL([+|-] years-months)
/*ISO date format*/
TO_YMINTERVAL([-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]])
Parameters
| Parameter | Description |
|---|---|
| [+|-] years-months | A string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type in the acceptable format.
|
| [-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]] | A string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type in the acceptable format. frac_secs indicates the fractional part of seconds, which is an integer in the range of [0, 999999999]. Notice: Blank spaces are not allowed in the value. |
Return type
The return type is INTERVAL YEAR TO MONTH.
Examples
The following example returns the datetime value that is 1 year 2 months after the current date and time.
obclient> SELECT SYSDATE,SYSDATE+TO_YMINTERVAL('01-02') FROM DUAL;
+-----------+--------------------------------+
| SYSDATE | SYSDATE+TO_YMINTERVAL('01-02') |
+-----------+--------------------------------+
| 18-NOV-21 | 18-JAN-23 |
+-----------+--------------------------------+
1 row in set