Introduction to Oracle INTERVAL YEAR TO MONTH Data Type

In Oracle database, the INTERVAL YEAR TO MONTH data type is used to represent the time interval between two dates or times in years and months. It can store a duration, such as “10 days 5 months”.

Syntax

The syntax for creating an INTERVAL YEAR TO MONTH data type is as follows:

INTERVAL YEAR[(year_precision)] TO MONTH

where year_precision represents the precision of the year. The precision range for YEAR TO MONTH data type is 0 to 9, with a default value of 2.

Use Cases

The INTERVAL YEAR TO MONTH data type is commonly used in scenarios that involve calculating the difference between two dates. For example, in banking operations, it can be used to calculate the maturity date of a loan repayment period.

Examples

Here are two examples of using the INTERVAL YEAR TO MONTH data type.

Example 1

Calculating the difference in months between two dates:

SELECT MONTHS_BETWEEN(TO_DATE('2023-03-01', 'yyyy-mm-dd'),
  TO_DATE('2021-01-01', 'yyyy-mm-dd'))
FROM dual;

Running the above query will yield the following result:

25.9032258064516

This result indicates that the difference in months between January 1, 2021, and March 1, 2023, is 25.9032258064516 months.

Example 2

Creating a table with a column of INTERVAL YEAR TO MONTH data type and inserting a row of data:

CREATE TABLE loan (
  id NUMBER(10),
  term INTERVAL YEAR(2) TO MONTH,
  start_date DATE,
  end_date DATE
);

INSERT INTO loan VALUES (1, INTERVAL '2-6' YEAR TO MONTH, TO_DATE('2022-01-01', 'yyyy-mm-dd'), TO_DATE('2024-06-01', 'yyyy-mm-dd'));

Running the above queries will create a table named “loan” with a column of INTERVAL YEAR TO MONTH data type and insert a record into the table.

Conclusion

In Oracle database, the INTERVAL YEAR TO MONTH data type is used to represent the time interval between two dates or times in years and months.