An Oracle function example is given below to calculate number of days between two dates. Just pass two dates as parameter to this function and it will return the number of days, which you can use in your SQL query.
Below is the function to get the number of days between two dates in Oracle:
CREATE OR REPLACE FUNCTION get_days (i_from_date IN DATE, i_to_date IN DATE)
RETURN NUMBER
IS
v_days NUMBER;
BEGIN
SELECT TRUNC (i_to_date) - TRUNC (i_from_date) + 1 INTO v_days FROM DUAL;
RETURN v_days;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
/
You can make it more custom as per your requirement. Below is the example for above function to use in SQL query:
SELECT get_days (TO_DATE ('11apr2016'), TO_DATE ('15apr2016')) AS days
FROM DUAL;
Or call it through PL/SQL anonymous block:
SET SERVEROUTPUT ON;
DECLARE
retval NUMBER;
i_from_date DATE;
i_to_date DATE;
BEGIN
i_from_date := '11apr2016';
i_to_date := '15apr2016';
retval := get_days (i_from_date, i_to_date);
DBMS_OUTPUT.put_line ('Number of days :' || retval);
END;
/
