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.

Advertisement
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
Related:  Limiting To Select Only 5 Check Boxes Out Of Ten In Oracle Forms

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has One Comment

Comments are closed.