[转]MONTHS_BETWEEN Function – Oracle to SQL Server Migration

king PHP 2023-02-27 1081浏览 0

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

Note that SQL Server DATEDIFF(month, date2, date1) function does not return exactly the same result, and you have to use an user-defined function if you need to fully emulate the Oracle MONTHS_BETWEEN function (see UDF’s code below).

Oracle:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
 
  -- 1-day difference  
  SELECT MONTHS_BETWEEN('2013-03-01', '2013-02-28') FROM dual;
  # 0.129032258
 
  -- Still 1-day difference but the result is different
  SELECT MONTHS_BETWEEN('2013-03-02', '2013-03-01') FROM dual;
  # 0.32258065

SQL Server:

DATEDIFF always returns an integer result.

-- 1-day difference, but 1 month returned (!)
  SELECT DATEDIFF(month, '2013-02-28', '2013-03-01');
  # 1
 
  -- Still 1-day difference but the result is different
  SELECT DATEDIFF(month, '2013-03-01', '2013-03-02');
  # 0

Also note that MONTHS_BETWEEN and DATEDIFF have different order of parameters. [转]MONTHS_BETWEEN Function – Oracle to SQL Server Migration

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('2013-03-13', '2013-02-13') FROM dual;
  # 1
 
  -- Between April 30 and January 31
  SELECT MONTHS_BETWEEN('2013-04-30', '2013-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('2013-03-01', '2013-02-28') FROM dual;
  # 0.129032258

Although there is just 1-day difference between February 28, 2013 and March 01, 2013, 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('2013-03-02', '2013-03-01') FROM dual;
   # 0.32258065

Now the fractional part is calculated as follows:

(2 – 1) / 31 = 0.32258065

SQL Server User-Defined Function to Emulate Oracle MONTHS_BETWEEN

You can use the following user-defined function to emulate Oracle MONTHS_BETWEEN function:

SQL Server:

CREATE FUNCTION MONTHS_BETWEEN (@date1 DATETIME, @date2 DATETIME) 
	RETURNS FLOAT
   AS
   /******************************************************************************
      PURPOSE: Emulate Oracle MONTHS_BETWEEN in SQL Server
 
      REVISIONS:
      Ver        Date             Author                                   Description
      ---------  ----------       ---------------                         ---------------------------
      1.1         2013-02-10  Dmitry Tolpeko (SQLines)       Created.
   ******************************************************************************/
   BEGIN
     DECLARE @months FLOAT = DATEDIFF(month, @date2, @date1);
 
     -- Both dates does not point to the same day of month
     IF DAY(@date1) <> DAY(@date2) AND
        -- Both dates does not point to the last day of month
        (MONTH(@date1) = MONTH(@date1 + 1) OR MONTH(@date2) = MONTH(@date2 + 1))
     BEGIN
        -- Correct to include full months only and calculate fraction
        IF DAY(@date1) < DAY(@date2)
          SET @months = @months + CONVERT(FLOAT, 31 - DAY(@date2) + DAY(@date1)) / 31 - 1;
        ELSE    
          SET @months = @months + CONVERT(FLOAT, DAY(@date1) - DAY(@date2)) / 31;
     END
 
     RETURN @months; 
   END;
   GO

Now you can use the UDF as follows:

SQL Server:

-- 1-day difference  
  SELECT dbo.MONTHS_BETWEEN('2013-03-01', '2013-02-28');
  # 0.129032258
 
  -- Still 1-day difference but the result is different (as in Oracle)
  SELECT dbo.MONTHS_BETWEEN('2013-03-02', '2013-03-01');
  # 0.32258065