TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type.
TO_YMINTERVAL accepts argument in one of the two formats:
SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
ISO duration format compatible with the ISO 8601:2004 standard
In the SQL format, years is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements.
In the ISO format, years and months are integers between 0 and 999999999. Days, hours, minutes, seconds, and frac_secs are non-negative integers, and are ignored, if specified. No blanks are allowed in the value.
The syntax for the 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.
Applies To:
· Oracle 9i, Oracle 10g, Oracle 11g
For example:
to_yminterval('03-11') | would return 3 years 11 months as an INTERVAL YEAR TO MONTH type |
to_yminterval('01-05') | would return 1 year 5 months as an INTERVAL YEAR TO MONTH type |
to_yminterval('00-01') | would return 0 years 1 month as an INTERVAL YEAR TO MONTH type |
The to_yminterval function is most commonly used to add an interval to a date field. For example, you may wish to add 1 year and 5 months to an order date.
select order_date, order_date + to_yminterval('01-05')
from orders;
This SQL statement would return the order date, as well as the order date plus 1 year and 5 months.
