更新时间:2025-06-04 GMT+08:00
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 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;