MONTHS_BETWEEN Function - Oracle to Snowflake Migration

In Oracle, the MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number.

Snowflake also provides the MONTHS_BETWEEN function.

Oracle:

  -- The same day of 2 different months
  SELECT MONTHS_BETWEEN(DATE '2022-03-28', DATE '2022-02-28') FROM dual;
  # 1
 
  -- The last day of 2 different months (compare with the previous example, the result is the same)
  SELECT MONTHS_BETWEEN(DATE '2022-03-31', DATE '2022-02-28') FROM dual;
  # 1  
 
  -- 1-day difference  
  SELECT MONTHS_BETWEEN(DATE '2022-03-01', DATE '2022-02-28') FROM dual;
  # 0.129032258
 
  -- Still 1-day difference but the result is different
  SELECT MONTHS_BETWEEN(DATE '2022-03-02', DATE '2022-03-01') FROM dual;
  # 0.32258065

Snowflake:

  -- The same day of 2 different months
  SELECT MONTHS_BETWEEN(DATE '2022-03-28', DATE '2022-02-28');
  # 1.000000
 
  -- The last day of 2 different months (compare with the previous example, the result is the same)
  SELECT MONTHS_BETWEEN(DATE '2022-03-31', DATE '2022-02-28');
  # 1.000000  
 
  -- 1-day difference  
  SELECT MONTHS_BETWEEN(DATE '2022-03-01', DATE '2022-02-28');
  # 0.129032
 
  -- Still 1-day difference but the result is different
  SELECT MONTHS_BETWEEN(DATE '2022-03-02', DATE '2022-03-01');
  # 0.32258

MONTHS_BETWEEN returns the number of full months between dates and a fractional part.

An integer value is returned only if:

• Both dates specify the same day of the month (February 13 and March 13 i.e.)
• Both dates are the last days of the months (January 31 and April 30 i.e.)

Oracle:

  -- Between March 13 and February 13
  SELECT MONTHS_BETWEEN('2022-03-13', '2022-02-13') FROM dual;
  # 1
 
  -- Between April 30 and January 31
  SELECT MONTHS_BETWEEN('2022-04-30', '2022-01-31') FROM dual;
  # 3

Fractional Part

The fractional part is calculated using the following formula:

Note that when MONTHS_BETWEEN calculates the fractional part, it considers that all months have 31 days.

Consider the following examples:

Oracle:

 -- 1-day difference  
  SELECT MONTHS_BETWEEN('2022-03-01', '2022-02-28') FROM dual;
  # 0.129032258

Although there is just 1-day difference between February 28, 2021 and March 01, 2021, MONTHS_BETWEEN considers Feb 29, Feb 30, Feb 31 and Mar 01:

Another example:

  -- Still 1-day difference but the result is different
  SELECT MONTHS_BETWEEN('2022-03-02', '2022-03-01') FROM dual;
   # 0.32258065

Now the fractional part is calculated as follows:

For more information, see Oracle to Snowflake Migration.