oracle 计算时间间隔月份数(不是时间意义上的月,而是中间间隔了几个yyyy-mm格式的月)

解决的不是时间意义上的月,而是中间间隔了几个yyyy-mm格式的月
时间意义上的直接date减法/30即可(虽然不严谨,实际每月不是30天)
本质上而言是12进制减法
例如2021-01-31 - 2022-09-21 = -21 (包含头尾)
2021-06-18更新

select 1 into minMax from dual where d1<d2; --如果是空值会报异常
select count(*) into minMax from dual where d1<d2; --改为这样然后还是判断minMax的值(本来想跳过ifelse判断的,不过失败了

直接上代码

CREATE OR REPLACE FUNCTION calc_month(
d1 IN date,d2 in date)
RETURN VARCHAR2
IS
countMonth number;
y1 number;
y2 number;
m1 number;
m2 number;
posApas number; --结果大于0还是小于0
minMax number;--第一个参数小于第二个参数时有值,否则为空
BEGIN
  if d1=d2 then
     countMonth := 1;
  else
     select count(*) into minMax from dual where d1<d2;
     if minMax=0 then 
       minMax :=null;
     else
       minMax :=1;
     end if;
     select nvl2(minMax,-1,1) into posApas from dual;
     select nvl2(minMax,to_number(to_char(d2,'yyyy')),to_number(to_char(d1,'yyyy'))) into y2 from dual;
     select nvl2(minMax,to_number(to_char(d1,'yyyy')),to_number(to_char(d2,'yyyy'))) into y1 from dual;
     select nvl2(minMax,to_number(to_char(d2,'mm')),to_number(to_char(d1,'mm'))) into m2 from dual;
     select nvl2(minMax,to_number(to_char(d1,'mm')),to_number(to_char(d2,'mm'))) into m1 from dual;
     if m1<=m2 then
       countMonth :=((y2-y1)*12+(m2-m1)+1)* posApas;
     else
       countMonth :=((y2-y1-1)*12+(m2-m1+12)+1)* posApas;
     end if;
  end if;
RETURN countMonth;
END;

select calc_month(date'2021-01-14',date'2022-09-30') from dual

date’2021-01-31’ 减去 date’2022-09-12’ 计算得到差了-21个月(包含头尾月一共有21个yyyy-mm的月)