오라클에서 제공하는 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
)