MONTHS_BETWEEN 함수를 이용해서 두 일자의 개월 수를 구할려고 합니다.
SELECT MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160131','yyyymmdd')) FROM DUAL UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160130','yyyymmdd')) FROM DUAL UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160129','yyyymmdd')) FROM DUAL
위 쿼리의 결과값은
---------------------------------------------------------------
1
0.96774
1
두 번째의 결과값도 1개월을 얻고자 합니다.
좋은 방법이 있을까요?
SELECT MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160131','yyyymmdd')) FROM DUAL UNION ALL SELECT ROUND(MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160130','yyyymmdd'))) FROM DUAL UNION ALL SELECT MONTHS_BETWEEN(TO_DATE('20160229','yyyymmdd'), TO_DATE('20160129','yyyymmdd')) FROM DUAL
이러케요???
WITH t AS ( SELECT '20160131' sdt, '20160229' edt FROM dual UNION ALL SELECT '20160130', '20160229' FROM dual UNION ALL SELECT '20160129', '20160229' FROM dual UNION ALL SELECT '20160229', '20160329' FROM dual UNION ALL SELECT '20160229', '20160330' FROM dual UNION ALL SELECT '20160229', '20160331' FROM dual UNION ALL SELECT '20160330', '20160430' FROM dual UNION ALL SELECT '20160331', '20160430' FROM dual ) SELECT sdt, edt , CASE WHEN e = LAST_DAY(e) AND TO_CHAR(s, 'dd') > TO_CHAR(e, 'dd') THEN ROUND(MONTHS_BETWEEN(e, s)) ELSE MONTHS_BETWEEN(e, s) END m FROM (SELECT sdt, edt , TO_DATE(sdt, 'yyyymmdd') s , TO_DATE(edt, 'yyyymmdd') e FROM t ) ;