Please help me -its very urgent

[deepti]

I had a table called ABC and there is a column called "EXPDATE". And now i have to update 500 columns of different expiration dates by one year less.

Ex: if my EXPDATE is 01/12/2007, My query should give an output like
01/12/2006


Please help me out.

[tabreaz]

You can try this query:

Check this query how it works:

SELECT sysdate, sysdate - TO_YMINTERVAL('01-00') FROM abc;

UPDATE abc
SET expdate = expdate - TO_YMINTERVAL('01-00');

[ales]

update abc set expdate=add_months(expdate,-12);

[jhmartin]

In future postings please put something about your actual question in the title instead of just 'help urgent'. It makes it easier for someone who knows about that particular problem to identify your post and help you.

[jrich]

Please note that adding or subtracting TO_YMINTERVAL('01-00') from a date can cause problems. For example, try:

SELECT to_date('29-feb-2008'),
to_date('29-feb-2008') - TO_YMINTERVAL('01-00')
FROM dual;

The problem is that Oracle just subtracts one year and comes up with 29-feb-2007 which is not a valid date. So using 01-00 you need to handle leap year dates.

Using add_months is probably a better choice in this case as it automatically adjusts the day to the last day of the month when the converted value is beyond the end of the month.

JR