Please help me -its very urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Please help me -its very urgent

  1. #1
    Join Date
    Mar 2007
    Posts
    1

    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
    01/12/2006


    Please help me out.

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    You can try this query:

    Code:
    Check this query how it works:
    
    SELECT sysdate, sysdate - TO_YMINTERVAL('01-00') FROM abc;
    
    UPDATE abc
    SET expdate = expdate - TO_YMINTERVAL('01-00');

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    An old-fashioned method:

    update abc set expdate=add_months(expdate,-12);
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    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.

  5. #5
    Join Date
    Jan 2006
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width