5 Replies Latest reply: Nov 18, 2012 9:07 AM by roots RSS

    Date arithmetic gives error PLS-00306

    roots
      Dear all,
      SQL> select*from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
      PL/SQL Release 10.2.0.1.0 - Production
      CORE    10.2.0.1.0      Production
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      Here is my code:
        1  declare
        2  v_date_time timestamp;
        3  v_dod  date;
        4  begin
        5  v_date_time:=to_date('11:20','HH12:MI AM');
        6  dbms_output.put_line('v_date_time ='||v_date_time);
        7  v_dod:=sysdate+v_date_time;
        8  dbms_output.put_line('v_dod ='||v_dod);
        9  exception when others then
       10  dbms_output.put_line('Error is'||sqlerrm);
       11* end;
      SQL> /
      v_dod:=sysdate+v_date_time;
             *
      ERROR at line 7:
      ORA-06550: line 7, column 8:
      PLS-00306: wrong number or types of arguments in call to '+'
      ORA-06550: line 7, column 1:
      PL/SQL: Statement ignored
      /
      How to add time with minutes with sysdate in this case..
      Please help me.
        • 1. Re: Date arithmetic gives error PLS-00306
          Solomon Yakobson
          roots wrote:
          How to add time with minutes with sysdate in this case..
          It is not clear what are you trying to do. If you want 11:20am today:
          select  trunc(sysdate) + interval '11:20' hour to minute eleven_twenty_am_today
            from  dual
          /
          
          ELEVEN_TWENTY_AM_TO
          -------------------
          11/18/2012 11:20:00
          
          SQL>
          If you want to add 11 hours and 20 minutes to sysdate:
          select  sysdate current_time,
                  sysdate + interval '11:20' hour to minute current_time_plus_11_20
            from  dual
          /
          
          CURRENT_TIME        CURRENT_TIME_PLUS_1
          ------------------- -------------------
          11/18/2012 08:41:21 11/18/2012 20:01:21
          
          SQL>
          SY.
          • 2. Re: Date arithmetic gives error PLS-00306
            Solomon Yakobson
            And same using date arithmetic which uses day as unit of measure (therefore 1 / 24 is one hour and 1 / 24 / 60 is one minute):
            select  trunc(sysdate) + 11 / 24 + 20 /24 / 60 eleven_twenty_am_today
              from  dual
            /
            
            ELEVEN_TWENTY_AM_TO
            -------------------
            11/18/2012 11:20:00
            
            SQL>
            And:
            select  sysdate current_time,
                    sysdate + 11 / 24 + 20 /24 / 60 current_time_plus_11_20
              from  dual
            /
            select  sysdate current_time,
                    sysdate + 11 / 24 + 20 /24 / 60 current_time_plus_11_20
              from  dual
            /
            SY.
            • 3. Re: Date arithmetic gives error PLS-00306
              roots
              Dear Solomon,
              It is not clear what are you trying to do. If you want 11:20am today:
              Actually v_date_time is the type of timestamp data type in my application.
              (oracle forms 10g).
              In this variable only I'm getting time with minutes..
              SQL>  declare
                2   v_date_time timestamp;
                3   v_dod  date;
                4   begin
                5   v_date_time:=to_date('11:20','HH12:MI AM');
                6   dbms_output.put_line('v_date_time ='||v_date_time);
                7   --v_dod:=sysdate+v_date_time;
                8  -- dbms_output.put_line('v_dod ='||v_dod);
                9   exception when others then
               10   dbms_output.put_line('Error is'||sqlerrm);
               11   end;
               12   /
              v_date_time =01-NOV-12 11.20.00.000000 AM
              So, i'm geting the above output.

              What i've do to is , only add the time "11.20" to sysdate.
              SQL> select sysdate from dual;
              
              SYSDATE
              --------------------
              18:nov:2012 07:25 pm
              so my final output should come as "18:nov:2012 11.20".(truncate the system time and add 11.20 with sysdate);

              regards,
              Karthi
              • 4. Re: Date arithmetic gives error PLS-00306
                Solomon Yakobson
                Timestamp datatype, same as date datatypes stores both date and time. The difference between date and timestamp is precision. Timestamp also stores fractional seconds. So you can't add two dates, two timestamps or date and timestamp. It simply makes no sense. What is sum of November 18 2012 9:16 am and November 18 2012 11:20 am? If you want to add 11 hours and 20 minutes to sysdate use, as I already posted:
                select  sysdate current_time,
                        sysdate + 11 / 24 + 20 /24 / 60 current_time_plus_11_20
                  from  dual
                / 
                
                CURRENT_TIME        CURRENT_TIME_PLUS_1
                ------------------- -------------------
                11/18/2012 09:18:14 11/18/2012 20:38:14
                
                SQL>
                SY.
                • 5. Re: Date arithmetic gives error PLS-00306
                  roots
                  Thanks you Solomon