本文转自：http://www.sqlines.com/oracle-to-sql-server/months_between
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](http://www.sqlines.com/_detail/exclamation.png?id=oracle-to-sql-server%3Amonths_between "[转]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
