MONTHS_BETWEEN ​

Return the number of months between two dates. The result may be positive or negative.

MONTHS_BETWEEN ( date1, date2 )

The inputs may have DATE, TIMESTAMP, or TIMESTAMPTZ data types. If one of the inputs is null, the result is null.

The function returns fractional values as required. The difference between the year and month values of the dates yields the whole number part of the result. The fractional part derives from the day and timestamp values of the dates, assuming a 31-day month.

If the first date is later than the second date, the result is a positive number; otherwise, the result is negative. For example, depending on the order of date expressions in this example, the function returns a positive number or a negative number:

premdb=# select months_between(matchday,current_date) num_months, matchday from match where htid=2 and atid=60;
   num_months     |      matchday       
-------------------+---------------------
 -97.3225806451613 | 2010-03-06 00:00:00
(1 row)

premdb=# select months_between(current_date,matchday) num_months, matchday from match where htid=2 and atid=60;
   num_months    |      matchday       
------------------+---------------------
 97.3225806451613 | 2010-03-06 00:00:00
(1 row)

If both dates fall on the same date within two different months, such as November 10th and September 10th, or the last day of the month, such as April 30th and February 28th (in a non-leap year), the result is a whole number based on the year and month values of the dates. Fractional numbers of hours are ignored. For example:

premdb=# select months_between(date '2017-09-10 12:15:00', date '2017-11-10 18:20:30') from sys.const;
 months_between 
----------------
            -2
(1 row)

premdb=# select months_between(date '2021-04-30', date '2021-02-28') from sys.const;
 months_between 
----------------
             2
(1 row)

premdb=# select months_between(date '2000-04-30', date '2000-02-28') from sys.const;
  months_between  
------------------
 2.06451612903226
(1 row)

Note that the last query specifies dates in 2000, which was a leap year.

Parent topic:Datetime Functions