Function to Return Number Of Specified Days Between Two Dates

In this post, we are using SQL to create a function that will return the number of specified days between two dates.

Introduction

 

Basically, an SQL function is a set of SQL statements that accept input parameters, perform actions, and return the result. A function can return a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables.

 

Prerequisite

1. Basic knowledge of Oracle SQL

In this blog, we will create a function using a simple approach that will return the number of selected days between given two input dates.

 

In the below example, we are creating a function named "F_GET_DAYS" with input parameters as V_DATE_FROM, V_DATE_TO which represents start date and enddate respectively. We are returning a number as output. We will declare a temporary variable to store to returning days i.e V_DAYS. We are going to compare selected days with the TO_CHAR function with the DY parameter.

SELECT TO_CHAR(V_DATE_FROM + LEVEL-1, 'DY') WWD_DAYS FROM DUAL CONNECT BY LEVEL <= (V_DATE_TO - V_DATE_FROM +1)

The above query returns the number of days between two dates including the start and end date. For example, if the start date is 10th September and the end date is 15th September, we will get an output as THU, FRI, SAT, SUN, MON, TUE.

SELECT COUNT(*) INTO V_DAYS FROM( SELECT TO_CHAR(V_DATE_FROM + LEVEL-1, 'DY') WWD_DAYS FROM DUAL CONNECT BY LEVEL <= (V_DATE_TO - V_DATE_FROM +1)) WHERE WWD_DAYS IN ('MON','WED');

The above query returns the number of Mondays and Wednesdays present between two dates. We can specify any days of the week where the condition according to our requirements.

 

The complete code for the function is given below and in case of any error, we are returning 0 days.

CREATE OR REPLACE FUNCTION F_GET_DAYS(V_DATE_FROM DATE, V_DATE_TO DATE)
RETURN NUMBER
IS
V_DAYS NUMBER;
BEGIN
SELECT COUNT(*) INTO V_DAYS FROM( SELECT TO_CHAR(V_DATE_FROM + LEVEL-1, 'DY') WWD_DAYS FROM DUAL CONNECT BY LEVEL <= (V_DATE_TO - V_DATE_FROM +1)) WHERE WWD_DAYS IN ('MON','WED');
RETURN V_DAYS;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE()|| ' - ' || SQLERRM());
RETURN 0;
END F_GET_DAYS;

For example, if we take the start date as 1^st January 2020 and the end date as 10^th January, then by calling the F_GET_DAYS function, we will get 6 days.