This discussion is archived
5 Replies Latest reply: Nov 18, 2012 7:07 AM by roots RSS

Date arithmetic gives error PLS-00306

roots Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks you Solomon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points