CODEWITHSUNDEEP
oracle-databaseplsql
tdcoder
tdcoder

Reputation: 21

'expression is of wrong type' error while calculating difference between two dates in oracle

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.

Upvotes: 0

Views: 59

Answers (1)

user5683823
user5683823

Reputation:

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?

Upvotes: 1

Related Questions