Function add day or add week
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Function add day or add week

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    Function add day or add week

    Is there any function in Oracle for add day or add week. I want to write a function that will add day or week. Your help will be appreciated.

    C.K.
    Apps DBA 11i

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Is this what you mean?

    Code:
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    13-JAN-03
    
    SQL> select sysdate+1 from dual;
    
    SYSDATE+1
    ---------
    14-JAN-03
    
    SQL> select sysdate+7 from dual;
    
    SYSDATE+7
    ---------
    20-JAN-03
    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Or....

    If you want a simple function do return a new date when specifing 'D' for day 'W' for week you could try the following

    Code:
    CREATE OR REPLACE FUNCTION date_advance
      (curr_date IN date, adv_flag in varchar)
    RETURN date
    IS
    BEGIN
      IF adv_flag = 'D' THEN
        RETURN curr_date+1;
      ELSIF adv_flag ='W' THEN
        RETURN curr_date+7;
      ELSE
        RETURN curr_date;
      END IF;
    END date_advance;
    This would give the following output:

    Code:
    SQL> select date_advance(sysdate,'D') from dual;
    
    DATE_ADVA
    ---------
    14-JAN-03
    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    JIm,

    I added the following lines to my function and it worked.


    SQL> select sysdate + 1 as ADD_DAY from dual
    2 /

    ADD_DAY
    ---------
    14-JAN-03

    SQL> select sysdate + 7 as ADD_WEEK from dual
    2 /

    ADD_WEEK
    ---------
    20-JAN-03

    Thanks.

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