반응형
add_months의 함수는
2-28일의 마지막 요일 일때 -1, +1 등과 같이 이전달, 다음달 값을 조회하면
3-31 or 1-31일과 같이 마지막 요일값이 출력된다...
이때는 오라클 add_months함수의 설정값이 마지막 요일값을 가지고 오도록 되어 있기 때문이다.
이문제를 해결하기 위해서는 별도로 함수를 작성해야한다...
Motivation
To use an Oracle function for adding months with the following characteristics:
- When the resulting month has as many or fewer days than the initial month, and when the initial day of the month is greater than the number of days in the resulting month, then the resulting day should fall on the last day of the resulting month (this is how add_months already works).
- When the resulting month has more days than the initial month, and when the initial day is the last day of the initial month, then the resulting day of the resulting month should be the same as the initial day (this is not how add_months works).
The Problem
As I had been using the Oracle add_months function for date calculations, I started noticing an unexpected and unintuitive result when a new date is calculated on the last day of certain months. For example,
SELECT add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;
ADD_MONTH
---------
31-MAR-09
I would have expected the resulting date to be 28-MAR-09.
Of course, in the case where the initial month contains more days than the resulting month, I get the results that I expect.
SELECT add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;
ADD_MONTH
---------
28-FEB-09
This feature appears to be part of the ANSI definition for interval math, but this result does not seem particularly intuitive to me.
Unfortunately, the numtoyminterval function only gives the result we expect when go from a month with fewer days to a month with more days, but when going from a month with more days to fewer, it raises an exception when calculating from the last day of the month (or from any day of the month that is greater than the number of days in the resulting month).
SELECT to_date('2009-02-28','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual;
TO_DATE('
---------
28-MAR-09
SELECT to_date('2009-01-31','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
The Function
The function itself is fairly straightforward using a combination of both add_months and numtoyminterval. When we are going from a month with more days to fewer days, then add_months yields the expected result. If we are going from a date with fewer days in the months to a date with more days in the month, then using numtoyminterval is safe because there will be no overflow.
CREATE OR REPLACE FUNCTION non_ansi_add_months
( vDate DATE,
vMonths INTEGER )
RETURN DATE AS
newDate DATE;
BEGIN
newDate := add_months(vDate, vMonths);
IF to_char(vDate, 'DD') < to_char(newDate, 'DD') THEN
newDate := vDate + numtoyminterval(vMonths, 'month');
END IF;
RETURN newDate;
END non_ansi_add_months;
The Result
This function now yields the results we expect.
SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;
NON_ANSI_
---------
28-MAR-09
SELECT non_ansi_add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;
NON_ANSI_
---------
28-FEB-09
The function even works as expected when adding negative months (calculating month intervals in the past). For example,
SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), -1) FROM dual;
NON_ANSI_
---------
28-JAN-09
SELECT non_ansi_add_months(to_date('2009-03-30','YYYY-MM-DD'), -1) FROM dual;
NON_ANSI_
---------
28-FEB-09
반응형
'Database > ORACLE' 카테고리의 다른 글
| SQL Plus 명령어 (0) | 2010.08.23 |
|---|---|
| 오라클 월(month) 관련 무조건 두자리로 만들기. (0) | 2010.08.18 |
| 오라클 exp 명령에서 Query 옵션 사용하기 (0) | 2010.08.05 |
| DBMS_METADATA (0) | 2010.08.05 |
| tnsnames.ora 등록 없이 Database Link 만들기 (0) | 2010.08.03 |