Skip to content
TheChatArchiveThe Chat Archive
chatgpt-4o-latest-20250326· OpenAIHugging Face LMArena Human Preference 140K

Please provide a Oracle SQL function that calculates the number of weekdays (business…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with o3-2025-04-16’s answer.
  1. User

    Please provide a Oracle SQL function that calculates the number of weekdays (business days) between two given dates

  2. 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_weekdays

    CREATE 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_date is after the end_date.
    • Uses TO_CHAR(..., 'D') with NLS_DATE_LANGUAGE = AMERICAN to 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_CALENDAR table and subtract matching holiday dates 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!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.