oracle 10g api to count how many saturday/sundays within 2 date parameters

however, your date values seems completely messed up?

Your dates should read:

01/06/2015     06/06/2015
01/06/2015     10/06/2015
11/06/2015     25/06/2015

Thank you Guy for the hint with the  calendar table, that's awesome ;-)

I have tried to find some alternative, and the simplest way IS the calendar table from what I see. especially as it is very reusable for many other scenarios/days etc ...

Just want to add to Alexander's original query.  It has a limitation that if the dates are more than a year apart it would not work.  It also does a lot more work than it has to.

If you change it to this:

SELECT Count(CASE 
               WHEN To_char(date_col, 'D') IN ( '6', '7' ) THEN 1 
               ELSE NULL 
             END) 
FROM   (SELECT To_date('&start_date', 'dd/mm/yyyy') 
               + ROWNUM - 1 date_col 
        FROM   dual 
        CONNECT BY LEVEL <= To_date('&end_date', 'dd/mm/yyyy') - 
                            To_date('&start_date', 'dd/mm/yyyy') + 1); 

Select all Open in new window

It is slightly more efficient and can handle any date range.  The where clause is removed as we can handle the limit with the connect by clause.

Thanks johnsone for making it "nice" ;-) It was just a quick & dirty one off the top of my head...

You can create a function to simply loop through and count

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date  DATE := p_start_date;
    v_count INTEGER := 0;
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN ('Sat', 'Sun')
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;

Select all Open in new window

I didn't actually look at the Saturday and Sunday determination in the query that Alexander posted.  Do not use the numbered day of the week like that.  It depends on NLS settings and could give incorrect results.  Use a case where the day is spelled out like DAY or DY.

The spelled out versions are also dependent on NLS settings; but I think those are easier to determine correctness than numbers.

Of course they do, what was I thinking.  I was looking at the documentation for format models and it clearly states that D is determined by NLS_TERRITORY and people do get tripped up on that setting and the changing start of the week.  However, in a completely separate section, it clearly states that DAY and DY depend on NLS_LANGUAGE.  Why put them in different places?

My guess is that you are better off going with the spelled out version as it is probably less confusing, but be aware that a user changing the value of NLS_LANGUAGE in their session will render your count useless.

select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual


I get 2 which is correct


I get 2 with either of the versions I have posted above.
What are you using?

what is the function code you are using?  If I have an NLS error,  what are your NLS settings?

>>>> btw I have altered the function to allow for dutch naming

if you used the 2nd form I posted, you shouldn't need to do that.

But again - what is the code you are running?

Please post the exact function code you are using, with any modifications
And the exact test query you used that generated 3


you and I are obviously doing something different but I can only see what I'm doing, show me what you are doing and I can diagnose

using this code

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date           DATE := p_start_date;
    v_count          INTEGER := 0;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(5) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(5) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;
/

Select all Open in new window

I get these results

SQL> select value  from v$nls_parameters where parameter = 'NLS_LANGUAGE';

VALUE
----------------------------------------------------------------
AMERICAN

SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;

TO_ TO_
--- ---
Sat Sun

SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;

COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
                                                    2

SQL> alter session set nls_language ='DUTCH';

Session altered.

SQL> select value  from v$nls_parameters where parameter = 'NLS_LANGUAGE';

VALUE
----------------------------------------------------------------
DUTCH

SQL> select TO_CHAR(DATE '2015-06-06', 'Dy'), TO_CHAR(DATE '2015-06-07', 'Dy') from dual;

TO TO
-- --
Za Zo

SQL> select count_weekend_days(date '2015-06-01', date '2015-06-10') from dual;

COUNT_WEEKEND_DAYS(DATE'2015-06-01',DATE'2015-06-10')
-----------------------------------------------------
                                                    2

Select all Open in new window

It almost appears that somehow the function is searching for Monday and Sunday since they would the only days that would occur 3 time between, 01/06/2016 and 10/06/2015. Are you sure your function created the abbreviations for a known Saturday and Sunday?

>>> IF TO_CHAR(v_date, 'DY') IN ('ZO', 'MA')

That doesn't appear to be correct

Sat,Sun should be Za, Zo  in Dutch,  not Zo,Ma

You are counting Sundays and Mondays,  and  June 1 - June 10  has 3 of those,  not 2.

If you count Saturdays and Sundays  (Za,Zo)  you'll get 2, not 3


If you use the 2nd form I posted, then the code is NLS-impervious.
You don't need to change anything to have it recognize Saturday and Sunday, regardless of what those days are named in other languages.

You might want to change the variable names and comments to correspond to native language, but no functional changes are needed

That is the issue. These are the days of the week in Dutch -
maandag Monday
dinsdag Tuesday
woensdag Wednesday
donderdag Thursday
vrijdag Friday
zaterdag Saturday
zondag Sunday

So the abbreviations should be ('ZA','ZO') and not ('ZO','MA')

I've got to teach these arthritic hands to type faster :-)

I just checked,  the maximum abbreviation length is 10 characters  (NLS_LANGUAGE=DIVEHI),  so, using the 2nd form expand the variables.  

This version really should be NLS-impervious, at least as of 12.1.0.2

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_date           DATE := p_start_date;
    v_count          INTEGER := 0;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday
BEGIN
    WHILE v_date <= p_end_date
    LOOP
        IF TO_CHAR(v_date, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
        THEN
            v_count := v_count + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    RETURN v_count;
END;
/

Select all Open in new window

In the interest of performance.  Wouldn't it be faster to keep the work in the SQL engine rather than having PL/SQL do all the work?  Seems like we are creating a looping structure where we don't really need one.  It can be simplified to this:

CREATE OR replace FUNCTION Count_weekend_days(p_start_date IN DATE, 
                                              p_end_date   IN DATE) 
RETURN NUMBER deterministic 
IS 
  v_count          INTEGER := 0; 
  -- Derive the Weekend abbreviations regardless of NLS settings by using known dates 
  v_saturday_abbrv VARCHAR2(10) := To_char(DATE'2015-06-06', 'Dy'); 
  -- a known Saturday 
  v_sunday_abbrv   VARCHAR2(10) := To_char(DATE '2015-06-07', 'Dy'); 
-- a known Sunday 
BEGIN 
    SELECT Count(1) 
    INTO   v_count 
    FROM   (SELECT To_char(p_start_date + ROWNUM - 1, 'Dy') dname 
            FROM   dual 
            CONNECT BY LEVEL <= p_end_date - p_start_date + 1) 
    WHERE  dname IN ( v_saturday_abbrv, v_sunday_abbrv ); 

    RETURN v_count; 
END; 

/ 

Select all Open in new window

Not that either way is right or wrong.  Just different.  If this is a function that would get called a lot, you may see a performance difference without the looping.

actually no,  the pl/sql should be faster  because switching from pl/sql to sql consumes resources.
the connect by is still a looping structure


here's a simple test to confirm

count_weekend_days - this is the final version, with 10 character abbreviations I posted above
count_weekend_dayssql - this is the code posted by johnsone

DECLARE
    c_loop_count CONSTANT INTEGER := 10;
    v_time1               TIMESTAMP;
    v_time2               TIMESTAMP;
    v_time3               TIMESTAMP;
    v_diff1               NUMBER;
    v_diff2               NUMBER;

    v_dummy               NUMBER;
BEGIN
    v_time1 := SYSTIMESTAMP;

    FOR n IN 1 .. c_loop_count
    LOOP
        FOR x IN 1 .. 100
        LOOP
            FOR y IN x + 1 .. x + 100
            LOOP
                v_dummy := count_weekend_days(SYSDATE + x, SYSDATE + y);
            END LOOP;
        END LOOP;
    END LOOP;

    v_time2 := SYSTIMESTAMP;

    FOR n IN 1 .. c_loop_count
    LOOP
        FOR x IN 1 .. 100
        LOOP
            FOR y IN x + 1 .. x + 100
            LOOP
                v_dummy := count_weekend_dayssql(SYSDATE + x, SYSDATE + y);
            END LOOP;
        END LOOP;
    END LOOP;

    v_time3 := SYSTIMESTAMP;

    v_diff1 :=
          EXTRACT(SECOND FROM (v_time2 - v_time1))
        + EXTRACT(MINUTE FROM (v_time2 - v_time1)) * 60
        + EXTRACT(HOUR FROM (v_time2 - v_time1)) * 3600
        + EXTRACT(DAY FROM (v_time2 - v_time1)) * 86400;

    v_diff2 :=
          EXTRACT(SECOND FROM (v_time3 - v_time2))
        + EXTRACT(MINUTE FROM (v_time3 - v_time2)) * 60
        + EXTRACT(HOUR FROM (v_time3 - v_time2)) * 3600
        + EXTRACT(DAY FROM (v_time3 - v_time2)) * 86400;

    DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
    DBMS_OUTPUT.put_line('Test1: ' || v_time1 || '   ' || v_time2 || '   ' || v_diff1);
    DBMS_OUTPUT.put_line('Test2: ' || v_time2 || '   ' || v_time3 || '   ' || v_diff2);
    DBMS_OUTPUT.put_line('Test1 took: ' || v_diff1 / v_diff2 || ' times as long as Test2');
    DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
END;

Select all Open in new window

which, on a small server I tested on, produced these results

--------------------------------------------------------------------------------
Test1: 10-JUN-15 10.46.43.917757 AM   10-JUN-15 10.46.47.057447 AM   3.13969
Test2: 10-JUN-15 10.46.47.057447 AM   10-JUN-15 10.46.55.494899 AM   8.437452
Test1 took: .3721135243199013161793394498718333449482 times as long as Test2
--------------------------------------------------------------------------------

Select all Open in new window

So, the purely pl/sql is significantly faster than the pl/sql and sql mix version.

Well now I got on a performance kick.  Best is to not do any looping of any kind and just calculate it directly.
We know there are 2 weekdays per full week, so use that as the base and then handle the end-points that may or may not fall on a weekend.

CREATE OR REPLACE FUNCTION count_weekend_days(p_start_date IN DATE, p_end_date IN DATE)
    RETURN NUMBER
    DETERMINISTIC
IS
    v_start          DATE := TRUNC(p_start_date);
    v_end            DATE := TRUNC(p_end_date);
    v_date           DATE := v_start;

    -- Derive the Weekend abbreviations regardless of NLS settings by using known dates
    v_saturday_abbrv VARCHAR2(10) := TO_CHAR(DATE '2015-06-06', 'Dy'); -- a known Saturday
    v_sunday_abbrv   VARCHAR2(10) := TO_CHAR(DATE '2015-06-07', 'Dy'); -- a known Sunday

    v_start_sunday   DATE;
    v_end_sunday     DATE := NEXT_DAY(v_end - 7, v_sunday_abbrv);

    v_count          INTEGER := 0;
BEGIN
    IF v_start = v_end AND TO_CHAR(v_start, 'Dy') IN (v_saturday_abbrv, v_sunday_abbrv)
    THEN
        v_count := 1;
    ELSIF v_start < v_end
    THEN
        IF TO_CHAR(v_date, 'Dy') = v_saturday_abbrv
        THEN
            v_count := v_count + 1;
            v_date := v_date + 1;
        END IF;

        IF v_date <= v_end AND TO_CHAR(v_date, 'Dy') = v_sunday_abbrv
        THEN
            v_count := v_count + 1;
            v_date := v_date + 1;
        END IF;

        v_start_sunday := NEXT_DAY(v_date - 7, v_sunday_abbrv);
        v_count := v_count + (v_end_sunday - v_start_sunday) / 7 * 2;

        IF TO_CHAR(v_end, 'Dy') = v_saturday_abbrv
        THEN
            v_count := v_count + 1;
        END IF;
    END IF;

    RETURN v_count;
END;

Select all Open in new window

This is MUCH more efficient than any of the previous options...
Test 1 is my previous best code, test 2 is the code in this post.

--------------------------------------------------------------------------------
Test1: 10-JUN-15 11.24.38.666404 AM   10-JUN-15 11.24.41.749738 AM   3.083334
Test2: 10-JUN-15 11.24.41.749738 AM   10-JUN-15 11.24.42.401349 AM   .651611
Test1 took: 4.73186302870884622880829206382335473158 times as long as Test2
--------------------------------------------------------------------------------

Select all Open in new window

I recommend NOT using any of the accepted answers above.

Instead - use the code you can find in http:#a40822772

Everything else is either wrong or needlessly slow and resource intensive

Sorry to put you on the performance chase.

Surprising (to me anyway) that the PL/SQL looping is faster.  I would have thought the internal looping in the SQL would be faster.

>>> Sorry to put you on the performance chase.

haha,  no problem,  It was kind of bugging me that I used looping from my very first post.  It seemed cumbersome even if simple from the very beginning.

the pl/sql vs sql seems expected to me.  SQL is a whole engine unto itself.  So you have to switch from pl/sql mode to sql mode and then do the parsing (even if its soft parsing) and invoke the execution engine and then whatever code happens inside the connect by and then you have to loop through those results of that connect by to do a count and then return that sql count back into pl/sql.

the pure pl/sql compiles down to almost nothing.  It's just a simple integer iterator.  If we compiled it down to c  instead of the default p-code,  there would be little to it.


and then the last version is nothing but some simple math and incrementing and that definitely makes sense that it should be faster and significantly so.

I guess that I was forgetting that your original didn't have a SQL call in it at all.  Good experiment though.

I fully agree that the pure pl/sql is the best in performance ...

but it has some serious limitations:
* it is limited to Saturday/Sunday calculations;
* as soon as you want to include holidays in the maths, or count only sundays, or only holidays etc ...
  => the only flexible way will be the calendar table.
* the calendar table is "portable as is" to any other db platform (oh well, yes, sorry, who would ever want to port from oracle to what's-the-name-of-that-other-vendor.... ?!?! ), while the pl/sql code would need a serious code rewrite.

so far for my 3c rant :)

* it is limited to Saturday/Sunday calculations;

   -- true, but that was the requested functionality.  Any function that hits a table will have to reference Saturday/Sunday or a "weekend_flag" or some similar indicator thus creating the same "limitation".  Of course the code can me modified, whether pl/sql or sql or a combination.  I don't see this as a positive or a negative, it's implicit in defining a function to return the requested result.

 * as soon as you want to include holidays in the maths, or count only sundays, or only holidays etc ...

   -- if the requirements change the function code must change, so yes, of course.  I don't think I'm catching your intent there.   For holidays specifically, those can often be derived by code, but a lookup table can be easier.  I'd have to see specific cases to determine whether io vs computation would be faster.  My guess is computation would win but it would require testing to verify.  With this function, the "easyness" isn't really a factor.  None of the variations are complex.

   => the only flexible way will be the calendar table.

 --- that's definitely not true, it's simply another option.  It does make some coding easier but I rarely take into account the developer's convenience.  The end functionality trumps a small amount of extra effort I might need to put into the code.


 * the calendar table is "portable as is" to any other db platform (oh well, yes, sorry, who would ever want to port from oracle to what's-the-name-of-that-other-vendor.... ?!?! ), while the pl/sql code would need a serious code


 --- A table is portable, and the sql to reference it would likely be too.   It's even more rare that I consider cross-platform portability when I write my code.  I write a common api and then underneath each platform gets its own code with whatever form is best for it.  I don't see trying to be generic as feature.

However, if this function is seldomly invoked then a portable, simple table-driven design is definitely a viable alternative.  I made the assumption it would be called frequently so I wanted to optimize.  If my assumption is wrong, there is no harm in having it be fast and the code, in all the forms is pretty simple.  So I wouldn't be concerned about future flexibility.  It's easily extensible as is.

"Portable" is truly a horrible concept.  Nothing is ever 100% portable.  And why am I writing generic portable code?  It tends to be horribly inefficient.  Plus, I'm paying an exorbitant fee to Oracle to be able to use their functionality, why aren't I using it?