Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Location
    Barcelona
    Posts
    42

    Unanswered: Adding units to timestamp



    I know how to add n time units to a timestamp:

    timestamp := current_timestamp + INTERVAL '1' MONTH;

    But what about adding units which are into a variable?

    units := 3;
    timestamp := current_timestamp + INTERVAL units MONTH; ?

    It does not work...

    Thanks.


  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Use ADD_MONTHS :

    Code:
    rbaraer@Ora10g> declare
      2  units number;
      3  begin
      4  units := 3;
      5  dbms_output.put_line('Current datetime : '||to_char(sysdate,'DD/MM/YYYY:HH24:MI'));
      6  dbms_output.put_line('Current datetime + 3 months : '||to_char(add_months(sysdate,units),'DD/MM/YYYY:HH24:MI'));
      7  end;
      8  /
    Current datetime : 28/02/2006:14:45
    Current datetime + 3 months : 31/05/2006:14:45
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    But what if you have to add/subtract years?

  4. #4
    Join Date
    Feb 2005
    Location
    Barcelona
    Posts
    42
    Thanks, but I would like to add a variable number of years, days, hours, minutes, seconds to a timestamp using INTERVAL (not month fractions). Something like:

    units:=3;
    timestamp := current_timestamp + INTERVAL units DAYS;

    Knowing that:

    timestamp := current_timestamp + INTERVAL '2' DAYS;

    is ok...

    Is it possible to use INTERVAL with a variable (no only constants)?

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Concerning INTERVAL, you can use NUMTODSINTERVAL or NUMTOYMINTERVAL , but you can clearly do whatever you want with classic date addition and add_months :

    For 1 year :
    Code:
    timestamp := add_months(current_timestamp, 12)
    For 1 month :
    Code:
    timestamp := add_months(current_timestamp, 1)
    For 1 day :
    Code:
    timestamp := current_timestamp + 1
    For 1 hour:
    Code:
    timestamp := current_timestamp + 1/24
    For 1 minute:
    Code:
    timestamp := current_timestamp + 1/1440
    For 1 second:
    Code:
    timestamp := current_timestamp + 1/86400
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    Feb 2005
    Location
    Barcelona
    Posts
    42

    Thank you. That's what I needed...

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Adding month intervals is also less reliable than ADD_MONTHS:

    Code:
    SQL> DECLARE
      2     ts TIMESTAMP := TIMESTAMP '2005-02-28 00:00:00';
      3  BEGIN
      4     ts := ts + INTERVAL '1' MONTH;
      5     DBMS_OUTPUT.PUT_LINE(ts);
      6  END;
      7  /
    
    28-MAR-05 12.00.00.000000 AM
    
    PL/SQL procedure successfully completed.
    
    SQL> ed
    
    Wrote file afiedt.buf
    
      1  DECLARE
      2     ts TIMESTAMP := TIMESTAMP '2005-01-31 00:00:00';
      3  BEGIN
      4     ts := ts + INTERVAL '1' MONTH;
      5* END;
    SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-01839: date not valid for month specified
    ORA-06512: at line 4
    Apparently this is by ANSI decree and not Oracle's fault:
    Oracle-WTF.blogspot.com/2006/02/stop-press-oracle-granted-license-to.html

Posting Permissions

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