Purpose
TO_YMINTERVAL() converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type into 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 is the fractional seconds and 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
The following example specifies a time interval of 2 years, 6 months, 10 days, 4 hours, 30 minutes, and 15 seconds.
obclient> SELECT TO_YMINTERVAL('P2Y6M10DT4H30M15S') FROM dual;
+------------------------------------+
| TO_YMINTERVAL('P2Y6M10DT4H30M15S') |
+------------------------------------+
| +000000002-06 |
+------------------------------------+
1 row in set
The following example specifies a negative time interval to subtract a time interval of 3 years, 1 month, and 5 days.
obclient> SELECT TO_YMINTERVAL('-P3Y1M5D') FROM dual;
+---------------------------+
| TO_YMINTERVAL('-P3Y1M5D') |
+---------------------------+
| -000000003-01 |
+---------------------------+
1 row in set