Oracle Function Example To Get Number Of Days Between Two Dates

by
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;
/
Calculate number of days between two dates in Oracle by a function

1 thought on “Oracle Function Example To Get Number Of Days Between Two Dates”

Leave a Comment