Oracle TO_YMINTERVAL function
Description
The TO_YMINTERVAL() function is used to converts a character string ( 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). In this format, year is an integer between 0 and 999999999, and months is an integer between 0 and 11. Additional blanks are allowed between format elements.
- ISO duration format compatible with the ISO 8601:2004 standard. In this 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.
Uses of Oracle TO_YMINTERVAL() Function
- Interval Conversion: Convert character strings to year-to-month intervals for precise time representations.
- Date-Time Arithmetic: Add or subtract year and month intervals to or from date values.
- Duration Management: Handle long-term durations such as contracts or project timelines in terms of years and months.
- Data Normalization: Standardize the representation of year and month intervals across different formats.
- Query Filtering: Retrieve data based on specific year-to-month intervals for analysis or reporting.
Syntax:
TO_YMINTERVAL ( ' { [+|-] years - months | ym_iso_format } ' )
ym_iso_format:
[-] P [ years Y ] [months M] [days D] [T [hours H] [minutes M] [seconds [. frac_secs] S ] ]
Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Example: Oracle TO_YMINTERVAL function
The following SQL query calculates for each employee a date one year three months after the hire date :
Sample table: employees
SQL> SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "15 months"
2 FROM employees;
Sample Output:
HIRE_DATE 14 months --------- --------- HIRE_DATE 15 months --------- --------- 17-JUN-03 17-AUG-04 21-SEP-05 21-NOV-06 13-JAN-01 13-MAR-02 03-JAN-06 03-MAR-07 21-MAY-07 21-JUL-08 25-JUN-05 25-AUG-06 05-FEB-06 05-APR-07 07-FEB-07 07-APR-08 17-AUG-02 17-OCT-03 16-AUG-02 16-OCT-03 28-SEP-05 28-NOV-06 . . .
Previous:
TO_DSINTERVAL
Next:
TRUNC(date)
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/oracle/datetime-functions/oracle-to_yminterval-function.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics