There is no built-in however, you can easily do this like this:


create or replace function weekends( p_date1 in date, p_date2 in date )
return number
as
l_date1 date default least(p_date1,p_date2);
l_date2 date default greatest(p_date1,p_date2);
l_days number default trunc(l_date2-l_date1)+1;
l_cnt number;
begin
select count(*) into l_cnt
from (select rownum r
from all_objects where rownum <= l_days)
where to_char(l_date1+r-1,'dy') in ( 'sat','sun' );

return l_cnt;
end;
/


I chose all_objects as the set to generate numbers from since all_objects typically has thousands of rows for us -- any suitably large table will do.



Here is an update to this question with a faster way to do this (much faster in fact)


Demidenko Sergey from Russia chimed in with an improved version that does weekends:

CREATE OR REPLACE
FUNCTION weekends2( p_date1 IN DATE, p_date2 IN DATE )
RETURN NUMBER
AS
l_monday varchar2(255)
default to_char( to_date( '03052001', 'mmddyyyy' ), 'DAY' );
l_date1 DATE DEFAULT LEAST(trunc(p_date1),trunc(p_date2));
l_date2 DATE DEFAULT GREATEST(trunc(p_date1),trunc(p_date2));
l_date2_monday DATE DEFAULT NEXT_DAY(l_date2,l_monday)-7;
l_date1_monday DATE DEFAULT NEXT_DAY(l_date1,l_monday)-7;
l_some_add NUMBER DEFAULT greatest((l_date2-l_date2_monday)-4,0);
l_some_sub NUMBER DEFAULT greatest((l_date1-l_date1_monday)-5,0);
BEGIN
RETURN
2*TRUNC((l_date2_monday-l_date1_monday)/7,0)+l_some_add-l_some_sub;
END;
/

It is a couple of order magnitude faster then the original one above. The original one above was taken from another example where I had to support arbitrary holidays as well - it had a table "holidays" and the query was:

select count(*) into l_cnt
from (select rownum r
from all_objects where rownum <= l_days)
where to_char(l_date1+r-1,'dy') in ( 'sat','sun' )
AND not exists ( select null
from holidays
where a_holiday = trunc(l_date1+r-1) );

it would skip saturday and sundays as well as any user defined holidays in the period.



Thanks for the feedback on this one.


followup to the comment:

Though they both provide solution to the weekend problem, the first example refers to a large table which may or may not exist for a user and the updated version uses hard code for the date part. The effective solution should be independent of both these factors. We should attempt for a stand-alone function.


Well, the first one refers to a large table that exists for everyone. ALL_OBJECTS is a standard data dictionary that is granted SELECT on to PUBLIC. If you do not have access to this view -- you have larger problems then getting this to work!! There will be thousands of other things that don't work in addition to this little routine. The first one is safe.

As for the second one using a "hard code" -- yes, it uses a hard coded date that is MONDAY. What we are doing there is getting the correct SPELLING for monday in your language so that the rest of the code works. It is indepedent of the hard code -- it works anywhere. Without the hard code it works only in english, the hard code is just a way to get the spelling for monday.