months_between

Database Type and Version

• Source database type and version: all Oracle versions
• Target database type and version: GaussDB of versions earlier than V2.0-2.7

Function replacement syntax:

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 calculatio.
   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;