Getting unexpected values for number of days between two dates in oracle-CodePudding

I am writing a SQL code which fetches two dates from the database and calculates the number of days between them. 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 number;

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;

I am not getting any error, but instead getting unexpected values for the number of days. Here is the output:

Statement processed.
246.4165625
238.4165625
231.4165625
222.4165625

I was expecting the number of days between the two dates(check the table). Can someone help me sort this out.

CodePudding user response：

CURRENT_DATE is an Oracle keyword that returns the current date. Name your column something that is not an Oracle keyword.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CURRENT_DATE.html

CodePudding user response：

As @Matthew McPeak pointed out, CURRENT_DATE is a built-in function and that function is being called rather than returning your column value.

If you want the column value then you need to prefix the column name with the table name/alias and use fine.current_date:

DECLARE
  roll_counter number:=1;
  initial_date date;
  final_date date;
  date_calc number;
BEGIN
  FOR roll_counter IN 1 .. 4 LOOP
    select date_of_issue
    into   initial_date
    from   borrower
    where  roll_no=roll_counter;

    select fine.current_date
    into   final_date
    from   fine
    where roll_no=roll_counter;

    date_calc:=final_date-initial_date;
    dbms_output.put_line(date_calc);
  END LOOP;
END;
/

Which, for your sample data, outputs:

Or you can use a single query (rather than multiple queries that are called in each loop iteration):

BEGIN
  FOR r IN (
    SELECT f.current_date - b.date_of_issue AS diff
    FROM   borrower b
           FULL OUTER JOIN fine f
           ON (b.roll_no = f.roll_no)
    WHERE  COALESCE(b.roll_no, f.roll_no) BETWEEN 1 AND 4
    ORDER BY COALESCE(b.roll_no, f.roll_no)
  ) LOOP
    dbms_output.put_line(r.diff);
  END LOOP;
END;
/

db<>fiddle here