Oracle MONTHS_BETWEEN function.

Preamble

Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.

Oracle/PLSQL syntax of MONTHS_BETWEEN function

MONTHS_BETWEEN( date1_id, date2_id )

Parameters and function arguments

• date1_id and date2_id dates used to calculate the number of months.

MONTHS_BETWEEN function returns a numeric value.

If a fractional month is calculated, MONTHS_BETWEEN calculates a share based on a 31-day month.

MONTHS_BETWEEN function in the following Oracle/PLSQL versions

Let’s consider some examples of MONTHS_BETWEEN function and learn how to use MONTHS_BETWEEN function in Oracle.

SELECT MONTHS_BETWEEN (TO_DATE ('2003/01/01', 'yyyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyyy/mm/dd')) FROM DUAL;

--Result: -2,419,3548

SELECT MONTHS_BETWEEN (TO_DATE ('2003/07/02', 'yyyyy/mm/dd'), TO_DATE ('2003/07/02', 'yyyyy/mm/dd')) FROM DUAL;

--Result: 0

SELECT MONTHS_BETWEEN (TO_DATE ('2003/08/02', 'yyyyy/mm/dd'), TO_DATE ('2003/06/02', 'yyyyy/mm/dd')) FROM DUAL;

--Result: 2

Frequently asked questions

Question: How do I calculate the number of years, months and days from the total number of days?

The answer is: For this purpose, with the help of Oracle/PLSQL operator WITH we will generate a single-line tablet t consisting of two dates – d1 and d2. Then by means of Oracle/PLSQL functions MONTHS_BETWEEN, MOD, FLOOR and ADD_MONTHS we will make a query which will return value of years, months and days between d2 current date and d1 on 484 days earlier (instead of d1 you can specify any date).

WITH t AS (
SELECT sysdate d2
, sysdate-484 d1
FROM dual
--484 - the number of days to be converted to the number of years, months and days
)
SELECT floor(months_between(d2,d1)/12) yy -- number of years
, floor(mod(months_between(d2,d1),12)) mm -- number of months
, d2-add_months(d1,floor(months_between(d2,d1)) dd -- number of days
FROM t;

--Result:
--YYY 1
--MM 3
--DD 27

As a result of the request we will receive 1 year, 3 months and 27 days. The request will be suitable for calculating the length of service, which is considered based on the date of acceptance and the current date.

Oracle Tutorial; Date Function: MONTHS_BETWEEN

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​