Home > Enterprise >  'expression is of wrong type' error while calculating difference between two dates in orac
'expression is of wrong type' error while calculating difference between two dates in orac

Time:09-06

I am trying to calculate difference between two dates and display the difference as days in the output. But I keep getting this error in Oracle Live sql:

ORA-06550: line 11, column 12:
PLS-00382: expression is of wrong type 

Here is the code:

create table borrower(
    roll_no number,
    date_of_issue date,
    name_of_book varchar(20),
    status varchar(10)
);

insert into borrower values(1,to_date('02-JAN-2022'),'dbms','issued');
insert into borrower values(2,to_date('10-JAN-2022'),'cns','issued');
insert into borrower values(3,to_date('17-JAN-2022'),'spos','issued');
insert into borrower values(4,to_date('26-JAN-2022'),'toc','issued');

create table fine(
    roll_no number,
    current_date date,
    amount number
);

insert into fine values(1,to_date('14-FEB-2022'),null);
insert into fine values(2,to_date('14-FEB-2022'),null);
insert into fine values(3,to_date('14-FEB-2022'),null);
insert into fine values(4,to_date('14-FEB-2022'),null);

DECLARE
roll_counter number:=1;
initial_date date;
final_date date;
date_calc date;

BEGIN
loop
select date_of_issue into initial_date from borrower where roll_no=roll_counter;
select current_date into final_date from fine where roll_no=roll_counter;
date_calc:=final_date-initial_date;
dbms_output.put_line(date_calc);
roll_counter:=roll_counter 1;
exit when roll_counter>4;
end loop;
END;
/

drop table borrower;
drop table fine;

Can someone help me sort out this error. Thank you in advance.

CodePudding user response:

Line 11 is this:

date_calc:=final_date-initial_date;

All three expressions in this formulas are declared as data type date.

Now do you see the problem? If you don't, think about this: what date is to_date('17-JAN-2022') minus to_date('11-DEC-2021')?

Look again at your problem description:

display the difference as days in the output.

What does that mean? Difference as days? What data type should that be?

  • Related