Problem
You try to add month or years to a date using interval year to month data type. But
Cause
You probably are trying from a day in a month that does not exist in the resulting month, e. g. 30th of January plus one month -> 30th of February 😓. The problem is, that adding year month interval is like just changing the month: 30 : 1 + 0 : 1 => 30 : 2 i. e. 30th of February. There is no logic to handle this within year month interval data type.
Solution
You need to employ the add_months function for that. It can be a bit tricky if you have got interval data like you can easily extract from range partion by date of the data dictionary. I wrote a function the takes a date and the function string to create an interval (as it is stored in the data dictionary) NUMTOYMINTERVAL(1, 'MONTH') and returns according date. Be aware that it uses calls to a
logger. You either need to install it as well or remove those calls.
/** Date calculations are not straight foreward such that adding a
* year-month interval to a date will probably result in a wrong result.
* Actually, you would be save until day 28, because each and every month
* has at least 28 days. Beyond that ...
* Examples with + NUMTOYMINTERVAL('1', 'MONTH')
*
* Finding Expectation Result
* ok 1999-01-15 1999-02-15 1999-02-15
* nok 1999-02-28 last of March 1999-03-28
* ok 1999-02-28 1999-03-28 1999-03-28
* nok 1999-01-29 1999-02-28 ORA-01839: date not valid for
* month specified
*
*
* @function ADD_MONTH_YEAR_INTERVAL
* @param in date I_DATE Base date on that the operation of the other
* parameter is executed.
* @param in varchar2 I_NUMTOYMINTERVAL_STRING NumToYMInterval operation
* string, e. g.
* NUMTOYMINTERVAL('1', 'MONTH')
* @return date Result of the NumToYMInterval operation on the base date.
* @throws PKG_EXCEPTIONS.CG_ERR_N_UNSUPP_OPERATION
*/
function ADD_MONTH_YEAR_INTERVAL(
I_DATE date,
I_NUMTOYMINTERVAL_OPERATION varchar2
)
return date
as
/* local constants */
CL_ROUTINE_NAME constant USER_OBJECTS.OBJECT_NAME%type :=
'ADD_MONTH_YEAR_INTERVAL';
CL_PARSER_PATTERN constant varchar2(1000 char) :=
'^\s*NUMTOYMINTERVAL\s*\(\s*(-?\s*\d+(\.\d+)?)\s*,\s*''(MONTH|YEAR)''\s*\)\s*$';
CL_SCALE_RESULT_PATTERN constant char(2 char) := '\3';
CL_VALUE_RESULT_PATTERN constant char(2 char) := '\1';
CL_STATEMENT constant char(1000 char) :=
'select add_months(:1, :2) from dual';
/* local variables */
VL_SCALE varchar2(1000 char);
VL_VALUE integer;
VL_RESULT_DATE date;
begin
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'I_DATE: ' ||
to_char(I_DATE, PKG_UTILITIES.CG_DATETIME_FORMAT),
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'I_NUMTOYMINTERVAL_OPERATION: ' ||
I_NUMTOYMINTERVAL_OPERATION,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'CL_PARSER_PATTERN: ' ||
CL_PARSER_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'CL_SCALE_RESULT_PATTERN: ' ||
CL_SCALE_RESULT_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'CL_VALUE_RESULT_PATTERN: ' ||
CL_VALUE_RESULT_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'CL_STATEMENT: ' ||
CL_STATEMENT,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
VL_SCALE := upper(
regexp_replace(
I_NUMTOYMINTERVAL_OPERATION,
CL_PARSER_PATTERN,
CL_SCALE_RESULT_PATTERN,
1, -- needed to get to the matching parameter
0, -- needed to get to the matching parameter
'i'
)
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'VL_SCALE: ' ||
VL_SCALE,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
VL_VALUE := regexp_replace(
I_NUMTOYMINTERVAL_OPERATION,
CL_PARSER_PATTERN,
CL_VALUE_RESULT_PATTERN,
1, -- needed to get to the matching parameter
0, -- needed to get to the matching parameter
'i'
);
PKG_PLNSQL_LOGGER.DEBUG(
MESSAGE => 'VL_VALUE: ' ||
VL_VALUE,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
if VL_SCALE is null or VL_VALUE is null then
PKG_PLNSQL_LOGGER.INFO(
MESSAGE => 'I_NUMTOYMINTERVAL_OPERATION: ' ||
I_NUMTOYMINTERVAL_OPERATION,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.INFO(
MESSAGE => 'CL_PARSER_PATTERN: ' ||
CL_PARSER_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.INFO(
MESSAGE => 'CL_SCALE_RESULT_PATTERN: ' ||
CL_SCALE_RESULT_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
PKG_PLNSQL_LOGGER.INFO(
MESSAGE => 'CL_VALUE_RESULT_PATTERN: ' ||
CL_VALUE_RESULT_PATTERN,
PACKAGE_NAME => CG_PACKAGE_NAME,
FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
);
raise_application_error(
PKG_EXCEPTIONS.CG_ERR_N_UNSUPP_OPERATION,
PKG_EXCEPTIONS.CG_ERR_D_UNSUPP_OPERATION
);
elsif VL_SCALE = 'YEAR' then
-- There is no add_year function so we have to base on months.
VL_VALUE := VL_VALUE * 12;
end if;
execute immediate CL_STATEMENT
into VL_RESULT_DATE
using I_DATE, VL_VALUE;
return VL_RESULT_DATE;
end ADD_MONTH_YEAR_INTERVAL;
BTW
You are save to use the day second interval data type for this kind of calculations.
