# months_between
**数据库类型与版本**
- 源库类型与版本：Oracle所有版本。
- 目标库类型与版本：GaussDB V2.0-2.7之前版本。
**转换替代方案：**
```
CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_last_day 
/* This function is used to find the last day in the month of the given date.
*/
( i_date      IN     TIMESTAMP WITH TIME ZONE )
RETURN TIMESTAMP(0) 
IMMUTABLE
AS  
    v_thedate        TIMESTAMP(0) := i_date; 
BEGIN
/* It finds the first day of the month and adds one month to the first day 
   and substracts one day from the calculated date.
*/
v_thedate := v_thedate - ((DATE_PART('DAY', v_thedate) - 1) * INTERVAL '1 DAY') + INTERVAL '1 MONTH - 1 DAY';
    RETURN v_thedate;
END;
CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_months_between 
/* This function is used to calculate the month difference.
   The day difference is also considered in the month difference calculation.
   So, it might return the decimal value.
*/
( i_date1       IN      TIMESTAMP WITH TIME ZONE
, i_date2       IN      TIMESTAMP WITH TIME ZONE ) 
RETURN NUMBER(24, 6)
IMMUTABLE
AS
     l_years_diff           INT           := 0;
     l_months_diff_wo_day   INT           := 0;
     l_months_diff_w_day    NUMERIC(24,6) := 0;
 l_date1_truc           TIMESTAMP(0);
 l_date2_truc           TIMESTAMP(0);
 l_date1_day            NUMERIC(2);
 l_date2_day            NUMERIC(2);
BEGIN
    -- if anyone of the inputs is NULL, return NULL
    IF i_date1 IS NULL
       OR i_date2 IS NULL 
    THEN
       RETURN NULL;
    END IF;
-- for calculating the year difference.
    l_years_diff         := DATE_PART('YEAR', i_date1) - DATE_PART('YEAR', i_date2);
-- for calculating the month difference using the value of year difference.
    l_months_diff_wo_day := l_years_diff * 12 + (DATE_PART('MONTH', i_date1) - DATE_PART('MONTH', i_date2)); 
-- truncate to date i.e., remove the time portion
l_date1_truc       := DATE_TRUNC('DAY', i_date1);
l_date2_truc       := DATE_TRUNC('DAY', i_date2);

-- find the day of the month i.e., "21st January 2017" will return 21.
l_date1_day        := DATE_PART('DAY', i_date1);
l_date2_day        := DATE_PART('DAY', i_date2);

-- check whether both the dates are last days of their months.
IF ( l_date1_day      = l_date2_day )
   OR
   ( l_date1_truc     = dsc_ora_ext.dsc_fn_last_day(l_date1_truc) 
     AND l_date2_truc = dsc_ora_ext.dsc_fn_last_day(l_date2_truc) ) 
 -- i.e., '31Jan2017' and '28Feb2017' are the last days of the months
THEN
l_months_diff_w_day := l_months_diff_wo_day;
ELSE
-- for calculating the month difference by considering the day difference also.
l_months_diff_w_day := ROUND(l_months_diff_wo_day + (l_date1_day - l_date2_day)/31, 6);
END IF;
RETURN l_months_diff_w_day;
END;
```