I have a quesiton on Date field. I'm trying to add 1 year to an existing date field and I get ' invalid date' error all the time.
Using below query
select cast(EFFECTIVE_DT as date format 'yyyy-mm-dd')+ interval '1' YEAR
Kindly help there!
This is totally unrelated to the original question, better open a new thread.
Never use Intervals for adding months/years, you'll always end on a non-existing day like 2018-02-29 or 2018-06-31.
When add_months(EFFECTIVE_DT,12) is still failing you got a date close to the max, probably 9999-12-31, then you need to apply a CASE.
Does your EFFECTIVE_DT has date like '2016-02-29' ? If you add 1 year using interval, it will lead to '2017-02-29', which is wrong as 2017 is not a leap year. Interval doesnt have date intelligence, no better not use it. Its better to use ADD_MONTHS function here. Please use
select ADD_MONTHS (EFFECTIVE_DT , 12), this would work.