w3resource

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)



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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