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

Oracle MONTHS_BETWEEN in Detail

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:

Condition	Fractional Part Calculation
If day_of_date1 > day_of_date2	(day_of_date1 - day_of_date2) / 31
If day_of_date1 < day_of_date2	(31 - day_of_date2 + day_of_date1) / 31

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:

(31 - 28 + 1) / 31 = 0.129032258

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:

(2 - 1) / 31 = 0.32258065

For more information, see Oracle to Snowflake Migration.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

MONTHS_BETWEEN Function - Oracle to Snowflake Migration

Oracle MONTHS_BETWEEN in Detail