오라클에서 제공하는 between_months 함수.

이 함수에 대응하는 함수를 작성해 보았다!
 (ALT_MONTH_BETWEEN으로 지칭)

$ cat c0.sql

set linesize 1024;
CREATE OR REPLACE FUNCTION ALT_MONTHS_BETWEEN
(
  P_TODATE    DATE
, P_FROMDATE  DATE
)
     RETURN  NUMBER
IS
     V_RET       NUMBER ;
     V_TODATE    TIMESTAMP ;
     V_FROMDATE  TIMESTAMP ;
     V_DIFF      NUMBER ;
BEGIN
     V_TODATE := P_TODATE ;
     V_FROMDATE := P_FROMDATE ;
     V_RET := ( EXTRACT(  YEAR FROM V_TODATE ) - EXTRACT(  YEAR FROM V_FROMDATE ) ) * 12
            + ( EXTRACT( MONTH FROM V_TODATE ) - EXTRACT( MONTH FROM V_FROMDATE ) )
            ;
     IF V_FROMDATE != LAST_DAY(V_FROMDATE) OR V_TODATE != LAST_DAY(V_TODATE)
     THEN
         IF ( EXTRACT(DAY FROM V_TODATE) = EXTRACT(DAY FROM V_FROMDATE) )
         THEN
             V_RET := V_RET + ( EXTRACT(DAY FROM ADD_MONTHS(V_TODATE,1)) - EXTRACT(DAY FROM V_FROMDATE) ) / 31.0 ;
         ELSE
             V_RET := V_RET + ( EXTRACT(DAY FROM ADD_MONTHS(V_TODATE,0)) - EXTRACT(DAY FROM V_FROMDATE) ) / 31.0
                            + ( EXTRACT(HOUR FROM V_TODATE) - EXTRACT(HOUR FROM V_FROMDATE) ) / 24.0 / 31.0
                            + ( EXTRACT(MINUTE FROM V_TODATE) - EXTRACT(MINUTE FROM V_FROMDATE) ) / 60.0 / 24.0 / 31.0
                            + ( EXTRACT(SECOND FROM V_TODATE) - EXTRACT(SECOND FROM V_FROMDATE) ) /60.0 / 60.0 / 24.0 / 31.0
                            ;
         END IF;
     END IF;
     RETURN V_RET ;
END;
/
show errors;



아래 구문을 통해 검증 완료

select
             to_char(todate,'YYYY-MM-DD HH24:MI:SS') todate
           , to_char(fromdate,'YYYY-MM-DD HH24:MI:SS') fromdate
           , todate - fromdate df
           , round(months_between(todate,fromdate),4) std_months
           , round(alt_months_between(todate,fromdate),4) alt_months
from (
          select
                        sysdate fromdate
                         , trunc(sysdate, 'dd') + level - 1 todate
                             from dual
                               connect by level < 36500
)


+ Recent posts