1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <span style= "font-size:10px" > 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개월을 얻고자 합니다. 좋은 방법이 있을까요? </span> |
1 2 3 | 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 |
이러케요???
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 ) ; |