Function that calculates number of days between 2 dates, accepts the start date and end date, then returns how many days it covers over that period

Question

I have to do something similar to this, and I need to spool file as well to css

CREATE OR REPLACE FUNCTION NumberOfDays
(in_date IN DATE)
RETURN NUMBER
IS
days NUMBER;
BEGIN
days:= TRUNC(SYSDATE - in_date);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

I need to create function NumberOfDays that calculates the number of days between 2 dates, accepts the start date and end date then returns how many days it covers over that period. — Peter Petrovski, Jun 05 '20 at 23:51

score 0 · Accepted Answer · answered Jun 05 '20 at 23:55

Your sample code is almost there. You just need to add a second in parameter. Also, you need to return the result, so it gets back to the caller. Finally, there is no need for an intermediate variable. You can compute and return immediatly.

create or replace function number_of_days(start_date in date, end_date in date)
return number
is
begin
    return trunc(end_date - start_date);
exception
    when others then
    dbms_output.put_line(sqlerrm);
end;
/

Then you can do:

select number_of_days(date '2020-06-01', date '2020-06-06') res from dual

Yields:

| RES |
| --: |
|   5 |

Thank you so much for your help and time! – Peter Petrovski Jun 06 '20 at 00:15 — Peter Petrovski, Jun 06 '20 at 00:15

Function that calculates number of days between 2 dates, accepts the start date and end date, then returns how many days it covers over that period

1 Answers1