Purpose
This function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH value, which can be used to add or subtract a date and time 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 CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that conforms to this parameter format.
|
| [-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]] | A string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type that conforms to this parameter format. frac_secs specifies the fractional part of the seconds, which is an integer in the range of [0,999999999].Notice The value cannot contain spaces. |
Return type
INTERVAL YEAR TO MONTH
Examples
Return the date and time value 1 year and 2 months after the current 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
Specify an interval of 2 years, 6 months, 10 days, 4 hours, 30 minutes, and 15 seconds. The following example shows how to do this:
obclient> SELECT TO_YMINTERVAL('P2Y6M10DT4H30M15S') FROM dual;
+------------------------------------+
| TO_YMINTERVAL('P2Y6M10DT4H30M15S') |
+------------------------------------+
| +000000002-06 |
+------------------------------------+
1 row in set
The following example shows how to specify a negative interval. Assume that you want to subtract 3 years, 1 month, and 5 days. The following example shows how to do this:
obclient> SELECT TO_YMINTERVAL('-P3Y1M5D') FROM dual;
+---------------------------+
| TO_YMINTERVAL('-P3Y1M5D') |
+---------------------------+
| -000000003-01 |
+---------------------------+
1 row in set