SQL & PL/SQL

Hi All,

I just want to get the exactly one year previous date by using the below query: for the leap year...

update day
SET FLAG = 'Y'
WHERE PERIOD_DATE BETWEEN TRUNC(ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12),'MON') and
ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12)

In the same time while we use the same query for the leap year date:

select * from day
WHERE PERIOD_DATE BETWEEN TRUNC(ADD_MONTHS(TO_DATE(20080229,'YYYYMMDD'),-12),'MON')
AND TO_DATE(20080229,'YYYYMMDD') + NUMTOYMINTERVAL(-1,'YEAR')

I receive the following error - ORA-01839: date not valid for month specified.
Problem:FLAG was incorrectly set to 'Y' for Feb 29 in 2004 during the report processing of Feb 28 data. Because of this, the extra day of Feb 29 2004 of Sales data was incorrectly being added to a report, thus overstating MTD sales of Feb 28 2004 by one full day. This appears to be caused by the fact that there were 28 days in Feb 2005 and 29 days in Feb 2004 becuase of the leap year. This will not cause problems until the next leap year.

Can you please give me the resolution of the above.

Thanks & Regards,
Satheesh

Message was edited by:
user565141

Message was edited by:
user565141