sql - Adding YEAR TO MONTH interval to TIMESTAMP value -

i have case have add year month interval timestamp value & achieve using way

select (end_date + numtoyminterval(2, 'month')) dual 

above code works end_date values except values.

for example, when end_date = 31-july-2013, expected result above code 30-sept-2013 throws error

ora-01839: date not valid month specified 

this because above code returns 31-sept-2013 invalid date.

is there alternate way achieve this?

(i can use add_months issue function returns date values & need timestamp return value)

am missing anything?

since end_date not have fractional seconds, or indeed time component, can use add_months , cast timestamp:

select cast(add_months(end_date, 2) timestamp) ... 

but add_months has own quirks. if original date last day of month, you'll last day of adjusted month - want if you're going shorter month in case, maybe not if you're going other way:

with t ( select to_timestamp('2013-07-31', 'yyyy-mm-dd') end_date dual union select to_timestamp('2013-06-30', 'yyyy-mm-dd') dual union select to_timestamp('2013-02-28', 'yyyy-mm-dd') dual union select to_timestamp('2012-02-29', 'yyyy-mm-dd') dual ) select end_date, cast(add_months(end_date, 2) timestamp) t;  end_date                       cast(add_months(end_date,2)ast ------------------------------ ------------------------------ 2013-07-31 00:00:00.000000     2013-09-30 00:00:00.000000 2013-06-30 00:00:00.000000     2013-08-31 00:00:00.000000 2013-02-28 00:00:00.000000     2013-04-30 00:00:00.000000 2012-02-29 00:00:00.000000     2012-04-30 00:00:00.000000 

or create own function handle bad dates, , adjust backwards until finds valid one:

create or replace function adjust_timestamp(orig_ts in timestamp,   months in number) return timestamp   new_ts timestamp;   offset number := 0;   bad_adjustment exception;   pragma exception_init(bad_adjustment, -01839); begin   while new_ts null loop     begin       new_ts := orig_ts - numtodsinterval(offset, 'day')         + numtoyminterval(months, 'month');     exception       when bad_adjustment         offset := offset + 1;         continue;     end;   end loop;   return new_ts; end; / 

this uses exception defined ora-01839 error code trap bad date, , in loop can work backwards (via offset) until finds 1 doesn't error.

with t ( select to_timestamp('2013-07-31', 'yyyy-mm-dd') end_date dual union select to_timestamp('2013-06-30', 'yyyy-mm-dd') dual union select to_timestamp('2013-02-28', 'yyyy-mm-dd') dual union select to_timestamp('2012-02-29', 'yyyy-mm-dd') dual ) select end_date, adjust_timestamp(end_date, 2) t;  end_date                       adjust_timestamp(end_date,2) ------------------------------ ------------------------------ 2013-07-31 00:00:00.000000     2013-09-30 00:00:00.000000 2013-06-30 00:00:00.000000     2013-08-30 00:00:00.000000 2013-02-28 00:00:00.000000     2013-04-28 00:00:00.000000 2012-02-29 00:00:00.000000     2012-04-29 00:00:00.000000 

which gives different results add_months version. need sure you're getting, , how want data behave.