oracle error ORA-01843 при попытке ввести месяцы от 0 до 11 в SQL-запросе

Question

Я использую следующую функцию, которая принимает три параметра date, годы для добавления и месяцы для добавления и возвращает окончательную дату.

    create or replace function date_add_year_month(p_date in date, p_years in number, p_months in number)
return date

as
v_years number;

v_date date;
begin
v_date := add_months(p_date, p_months);
v_years := p_years + extract(year from v_date);
return  to_date(v_years || (extract(month from v_date)) || (extract (day from v_date)), 'YYYYMMDD');
end;
/

Теперь, если я запускаю следующие тестовые данные, я получаю ошибку недопустимого месяца.

 select date_add_year_month(date '2009-10-28', 0, 10) from dual;
    select date_add_year_month(date '2009-10-28', 0, 11) from dual;
    select date_add_year_month('2009-Jan-01', 5, 0) from dual;
    select date_add_year_month(date '2009-Oct-28', -1, -5) from dual;

но как только я начинаю вводить значение месяца 12 или выше в функцию, он начинает давать мне вывод.

select date_add_year_month(date '2009-10-28', 0, 12) from dual;
select date_add_year_month(date '2009-10-28', 0, 13) from dual;
select date_add_year_month(date '2009-Oct-28', 1, 26) from dual;
select date_add_year_month(date '2009-10-28', 0, 36) from dual;
select date_add_year_month(date '2009-10-28', -1, 26) from dual;

Answer 1

Когда вы делаете extract (month from v_date) Результатом является число, которое может быть одной цифрой. Добавление 10 месяцев к 2009-10-28 дает вам 2010-08-28, а извлечение номера месяца из этого дает вам 8 - а не 08, В этом случае вы добавляете ноль лет, поэтому в итоге вы пытаетесь сделать:

to_date(v_years || (extract(month from v_date)) || (extract (day from v_date)), 'YYYYMMDD')

который становится:

to_date(2009 || 8 || 28), 'YYYYMMDD')

после неявного преобразования в строки:

to_date('2009828', 'YYYYMMDD')

... и 82 не является действительным номером месяца. Когда вы проходите 11 месяцев, вы получаете 92. Когда вы проходите 12, но возвращаетесь к октябрю, выписка получает 10, и вы возвращаетесь к нужному количеству символов в вашей строке - в соответствии с вашей моделью формата. (Если вы укажете дату в ноябре, то через 11 месяцев все будет в порядке, так как это все равно закончится в октябре, и выписка даст вам 10, что снова даст вам действительный месяц).

То же самое происходит со всеми вашими другими комбинациями даты и корректировки. Вы можете получить аналогичную проблему с номером дня.

Вы можете добавить слева извлеченные числа к двум символам, но сделать это будет гораздо проще:

return add_months(p_date, (p_years * 12) + p_months);

Или вообще не используйте свою собственную функцию, так как выполнение этого не намного сложнее, чем вызов функции.