Please provide a Oracle SQL function that calculates the number of weekdays (business…
- User
Please provide a Oracle SQL function that calculates the number of weekdays (business days) between two given dates
- Assistant
Sure! Below is an Oracle SQL user-defined function that calculates the number of weekdays (i.e., business days, Monday through Friday) between two dates — excluding Saturday and Sunday. It is set-based, uses no
CONNECT BY, and is efficient even for large date ranges.
✅ Function:
get_weekdaysCREATE OR REPLACE FUNCTION get_weekdays ( start_date IN DATE, end_date IN DATE ) RETURN NUMBER IS total_days NUMBER; full_weeks NUMBER; remaining_days NUMBER; start_day_num NUMBER; weekend_days NUMBER := 0; BEGIN -- Handle nulls or invalid input IF start_date IS NULL OR end_date IS NULL THEN RETURN NULL; END IF; -- Swap dates if start_date > end_date IF start_date > end_date THEN RETURN get_weekdays(end_date, start_date); END IF; -- Total days inclusive total_days := end_date - start_date + 1; -- Full weeks and remaining days full_weeks := TRUNC(total_days / 7); remaining_days := MOD(total_days, 7); -- Day of the week (1 = Sunday, 7 = Saturday) start_day_num := TO_NUMBER(TO_CHAR(start_date, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')); -- Now calculate extra weekend days in the remaining days FOR i IN 0 .. remaining_days - 1 LOOP DECLARE day_of_week NUMBER := MOD(start_day_num - 1 + i, 7) + 1; BEGIN IF day_of_week = 1 OR day_of_week = 7 THEN weekend_days := weekend_days + 1; END IF; END; END LOOP; -- Business days = total days - (2 days per full week + remaining weekend days) RETURN total_days - (full_weeks * 2) - weekend_days; END; /
🧪 Example Usage
SELECT get_weekdays(DATE '2023-10-01', DATE '2023-10-31') AS business_days FROM dual;Result:
22
🧠 Notes:
- The function is inclusive: both start and end dates are counted.
- Automatically swaps the dates if the
start_dateis after theend_date. - Uses
TO_CHAR(..., 'D')withNLS_DATE_LANGUAGE = AMERICANto ensure Sunday is 1 and Saturday is 7. - You can modify the logic if holidays need to be excluded by extending it to check against a holiday table.
🏁 Optional: Add Holidays (Advanced Version)
If you want to exclude public holidays, you could include a
HOLIDAY_CALENDARtable and subtract matchingholidaydates that fall on weekdays. Let me know if you'd like that too!
Let me know if you'd like this version turned into a PL/SQL procedure, package, or support holiday exclusions — happy to extend it!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.