Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions / Oracle MONTHS_BETWEEN

Oracle MONTHS_BETWEEN

The Oracle MONTHS_BETWEEN() function returns the number of months between two dates.

Syntax

The following illustrates the syntax of the Oracle MONTHS_BETWEEN() function:

MONTHS_BETWEEN(minuend_date, subtrahend_date );

Arguments

The Oracle MONTHS_BETWEEN() function requires two arguments, each of which can be a DATE or expression evaluates to a DATE:

1) minuend_date

The minuend_date is a date which is subtracted from.

2) subtrahend_date

The subtrahend is also a date which is to be subtracted.

Return value

The MONTHS_BETWEEN() function returns the number of months between two dates which is:

  • a positive integer if minuend_date is later than subtrahend_date.
  • a negative integer if minuend_date is earlier than subtrahend_date.
  • an integer if minuend_date and subtrahend_date are either the same days or they are both the last days of the months.

In other cases, the MONTHS_BETWEEN() function will calculate the fractional portion of the result based on the 31-day month and also consider the difference in time parts of  minuend_date and subtrahend_date.

Examples

Let’s take some example to get a better understanding of how the MONTHS_BETWEEN() function works.

A) Get the difference in months of dates with the same day

The following example returns a difference in months between two dates:  July 01 2017 and January 01 2017:

SELECT MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' ) MONTH_DIFF FROM DUAL;

The result is 6 months as shown below:

Oracle MONTHS_BETWEEN

B) Both date arguments are the last days of the months

The following statement returns 1 month because both arguments are the last day of the months:

SELECT MONTHS_BETWEEN( DATE '2017-03-31', DATE '2017-02-28' ) MONTH_DIFF FROM DUAL;

C) Both date arguments are not the same day

The following example returns a decimal because the days of the date arguments are not the same.

SELECT MONTHS_BETWEEN( DATE '2017-07-31', DATE '2017-08-15' ) MONTH_DIFF FROM DUAL;

It returns about -0.5 month:

-0.4838709677419354838709677419354838709677

In this tutorial, you have learned how to use the Oracle MONTHS_BETWEEN() function to get the number of months between two dates.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle SYSTIMESTAMP
Next Oracle NEXT_DAY

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Date Functions

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEXT_DAY
  • NEW_TIME
  • ROUND
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR
  • TO_DATE
  • TRUNC
  • TZ_OFFSET

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.