Please help me -its very urgent
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
Please help me out.
You can try this query:
Check this query how it works:
SELECT sysdate, sysdate - TO_YMINTERVAL('01-00') FROM abc;
SET expdate = expdate - TO_YMINTERVAL('01-00');
An old-fashioned method:
update abc set expdate=add_months(expdate,-12);
The whole difference between a little boy and an adult man is the price of toys
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.
Please note that adding or subtracting TO_YMINTERVAL('01-00') from a date can cause problems. For example, try:
to_date('29-feb-2008') - TO_YMINTERVAL('01-00')
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.
Click Here to Expand Forum to Full Width