Create Custom Function for Date Difference Excluding Weekends and Holidays in Oracle SQL

create custom function for date difference excluding weekends and holidays in oracle sql

You do not need to use a row generator to enumerate every day to get the number of week days - it can be done using a simple calculation:

From my answer here:

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE
) RETURN NUMBER DETERMINISTIC
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
  p_holiday_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := LEAST( in_start_date, in_end_date );
  p_end_date   := GREATEST( in_start_date, in_end_date );

  -- 5/7 * ( Number of days between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );

  SELECT COALESCE(
           SUM(
             LEAST( p_end_date, holiday_date + INTERVAL '1' DAY )
             - GREATEST( p_start_date, holiday_date )
           ),
           0
         )
  INTO   p_holiday_days
  FROM   Holidays
  WHERE  HOLIDAY_DATE BETWEEN TRUNC( p_start_date )
                      AND     TRUNC( p_end_date )
  AND    HOLIDAY_DATE - TRUNC( HOLIDAY_DATE, 'IW' ) < 5;

  RETURN GREATEST( p_working_days - p_holiday_days, 0 );
END;
/

different between two dates without weekend oracle function

I think you need to consider the following code:

CREATE OR REPLACE FUNCTION FN_GETBUSINESSDAYSINTERVAL (
    V_BEGIN_DATE DATE,
    V_END_DATE DATE
) RETURN NUMBER AS
    V_DAYSINBETWEEN      NUMBER := 0;
    V_BUSDAYSINBETWEEN   NUMBER := 0;
BEGIN
    WITH DAYS AS (
        SELECT V_BEGIN_DATE + LEVEL AS DAY_DATE,
               TO_CHAR(V_BEGIN_DATE + LEVEL, 'D') DAY_OF_WEEK
          FROM DUAL
        CONNECT BY LEVEL <= ( V_END_DATE - V_BEGIN_DATE )
    )
    SELECT
        V_END_DATE - V_BEGIN_DATE AS DAYS_INBETWEEN, --total number of days in between
        SUM(CASE WHEN DAYS.DAY_OF_WEEK NOT IN( 7, 1 )
                 AND HT.DATA IS NULL THEN 1 END) BUSINESS_DAYS_INBETWEEN -- total number of business days inbetween
    INTO V_DAYSINBETWEEN, V_BUSDAYSINBETWEEN
    FROM DAYS LEFT
         JOIN DNI_SWIATECZNE HT ON HT.DATA = DAYS.DAY_DATE;

    RETURN COALESCE(V_BUSDAYSINBETWEEN, 0);

EXCEPTION 
    WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

Exclude weekends and public holiday for SQL datediff

Like has been mentioned, the best way is to create a Calendar Table and then use the Working Days column, or whatever it is you called it, to calculate the difference.

This is pseudo-SQL, in the absence of a working Calendar Table, but should get you there:

SELECT YT.{YourColumn},
       WD.WorkingDays
FROM YourTable YT
     CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
                  FROM CalendarTable CT
                  WHERE CT.[DateColumn] >= YT.StartingDateColumn
                    AND CT.[DateColumn] <= YT.EndingDateColumn
                    AND CT.WorkingDay = 1) WD

---

Related Topics