NUMTOYMINTERVAL

Description of numtoyminterval.eps follows
  • NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. 
  • The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. 
  • The argument interval_unit can be of CHARVARCHAR2NCHAR, or NVARCHAR2 data type. 
  •  The value for interval_unit specifies the unit of n and must resolve to one of the following string values: ‘YEAR‘ or ‘MONTH
  • interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

Example

Example 2

Let’s say you have a table named employees with columns employee_id, hire_date, and name. You want to retrieve the employees who have been working for more than 3 years.

sql

In this example:

  1. MONTHS_BETWEEN(SYSDATE, hire_date) calculates the number of months between the current date (SYSDATE) and the employee’s hire_date.
  2. NUMTOYMINTERVAL(..., 'MONTH') converts the result into an interval in terms of years and months.
  3. The WHERE clause filters out the employees whose employment duration is less than or equal to 3 years.

Adjust the table and column names accordingly based on your actual database schema. This example assumes that hire_date is a date column in the employees table.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top