Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Amin.

Asked: February 28, 2001 - 1:15 pm UTC

Answered by: Tom Kyte - Last updated: January 24, 2012 - 1:37 pm UTC

Category: Database - Version: 7.3.4.5

Viewed 1000+ times

You Asked

Is there a standard function which can count number of Saturday's and Sunday's in a given period.

For e.g

From 01-Jan-2001 to 28-Feb-2001
number of Sat and Sunday's
Ans would be 16

if not, can u advise a idea to count weekends.

and we said...

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.

and you rated our response

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

March 15, 2001 - 7:57 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia


Weekend

April 01, 2001 - 12:20 pm UTC

Reviewer: Rajeev Ranjan from Mumbai, India

Yes, it's helpfull.


Computation of Weekend days.

April 01, 2001 - 6:32 pm UTC

Reviewer: Gulamali Zakir from Chicago, USA.

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.

Weekend

April 04, 2001 - 1:36 pm UTC

Reviewer: Tiamiyu Salau from North Carolina

This also works

CREATE OR REPLACE FUNCTION wk_end(date1 IN DATE DEFAULT SYSDATE,date2 IN DATE DEFAULT SYSDATE+7) RETURN number AS
max_date date := GREATEST(TRUNC(date1),TRUNC(date2));
min_date date := LEAST(TRUNC(date1),TRUNC(date2));
no_of_wkend number := 0;
count_date date := min_date;
BEGIN
WHILE count_date <= max_date LOOP
IF TO_CHAR(count_date,'DY') IN ('SAT','SUN') THEN
no_of_ekend := no_of_wkend+1;
END IF;
count_date := count_date+1;
END LOOP;
END;


April 08, 2001 - 2:28 pm UTC

Reviewer: Gautam from BC, Canada


Ultimate

April 19, 2001 - 1:06 am UTC

Reviewer: Harish Babu.N from Bangalore,India

As always Tom has been Ultimate in giving answers

Count No. of weekends

April 27, 2001 - 12:36 am UTC

Reviewer: Chetan Kashikar from Auckland, New Zealand

I have a better solution for this.

Create a function as below

create or replace function
count_weekends(p_start_date date
,p_end_date date
) RETURN NUMBER IS
p_count number := 0;
tdate date ;
x varchar2(20);
begin
tdate := p_start_date;
LOOP
if tdate > p_end_date then
exit;
end if;
x := to_char(tdate,'DAY');
if rtrim(x) in ('SATURDAY','SUNDAY') then
p_count := p_count +1;
end if;
tdate := tdate + 1;
end loop;
return p_count;
end;
/
select count_weekends(to_date('01-JAN-01',
'dd-mon-rr'),
to_date('31-DEC-01','dd-mon-rr'))
no_of_weekends from dual
/

Hope this helps

questions of week

April 28, 2001 - 3:55 pm UTC

Reviewer: Asif Mahmood from Karachi, Pakistan

it was really helpful for me



Excellence

May 03, 2001 - 5:30 pm UTC

Reviewer: Absalam Hichri from Los Angeles California


I find that this function can be one of the best solutions to this problem. As always it is Tom's creation and Search .Thank you Tom for your continuous searches


Count of Week Ends

May 09, 2001 - 5:11 am UTC

Reviewer: Anand Singh from Delhi-India

It is very usefull to me.

It Is Just Great

May 18, 2001 - 6:28 pm UTC

Reviewer: Ahmad Sha Khan from Karachi,Pakistan

It is an amazing and easy solution to this problem

June 03, 2001 - 3:27 am UTC

Reviewer: Vijay from Singapore


June 07, 2001 - 5:14 pm UTC

Reviewer: Alan from U.S.A


I have another solution for this

June 15, 2001 - 3:08 am UTC

Reviewer: sadiq.n from chennai

declare
m_var varchar2(4);
m_var2 varchar2(15);
y varchar2(4):='&year';
mm number;
begin
for j in 1..12 loop
select to_number(to_char(last_day(to_date(lpad(j,2,0)||y,'mmyyyy')),'dd')) into mm from dual;
for i in 1..mm loop
select to_char(to_date(lpad(i,2,0)||lpad(j,2,0)||y,'ddmmyyyy'),'dy'),
to_char(to_date(lpad(i,2,0)||lpad(j,2,0)||y,'ddmmyyyy'),'dd-mon-yyyy')
into m_var,m_var2 from dual;
if m_var in('sat','sun') then
insert into week_end values(m_var,m_var2);
end if;
end loop;
end loop;
end;


July 09, 2001 - 5:42 pm UTC

Reviewer: Miguel Icaza from Troy, Mi

No only the Demidenko Sergey solution is faster: it works!. The COUNT_WEEKENDS function does not work more than 18 years range; at least in my site, where ALL_OBJECTS has 6723 rows.

Sonali Kelkar

August 13, 2001 - 4:23 pm UTC

Reviewer: Sonali Kelkar from MA USA

This one works fast:
Create or replace function num_Business_Days(start_date in date, end_date in date)
return number is
countBusiness number := 0; /* counter for business days */

begin

countBusiness:= (to_date(end_date,'dd-mon-yy')- to_date(start_date, 'dd-mon-yy')) +1
- (Next_Day(to_date(end_date,'dd-mon-yy'), 'Sunday')
- Next_Day(to_date(start_date,'dd-mon-yy'), 'Sunday') )/7
- (Next_Day(to_date(end_date,'dd-mon-yy'), 'Saturday')
- Next_Day(to_date(start_date,'dd-mon-yy'), 'Saturday') )/7;

return (countBusiness);
end;
/

August 29, 2001 - 10:45 am UTC

Reviewer: Chandra Mohan Reddy from Hyderabad India

Tom, Pretty Useful one.

How to get coming saturday by a given date?

March 13, 2003 - 4:27 pm UTC

Reviewer: Steve from NYC, USA

Hi Tom,

What I am going to do is to get coming saturday by a given date. after I read this thread i come up the following sql:

select trunc(:mydate + rr)
from (
select to_char(:mydate + rownum,'dy') r1, rownum as rr
from user_Objects
where rownum<8
)t
where t.r1 ='sat'

But the performance is too bad, it took about 7 seconds.
Do you have any better and faster way to do it?

Thanks!

Steve


Tom Kyte

Followup  

March 14, 2003 - 5:46 pm UTC

You must have an awful slow system:

ops$tkyte@ORA920> declare
  2          mydate date := sysdate;
  3          x date;
  4  begin
  5  select trunc(mydate + rr) into x
  6  from (
  7    select to_char(mydate + rownum,'dy') r1, rownum as rr
  8    from user_Objects
  9    where rownum<8
 10  )t
 11  where  t.r1 ='sat';
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


But anyway -- look at next_day
ops$tkyte@ORA920> select next_day( sysdate, 'sat' ) from dual;

NEXT_DAY(
---------
15-MAR-03

 

September 13, 2006 - 8:20 am UTC

Reviewer: A reader

helpful...
Thanks tom..

April 22, 2010 - 2:38 am UTC

Reviewer: Ashraf from india

i read ur ans.. thanks for this and want support more help

January 24, 2012 - 8:24 am UTC

Reviewer: Reader

Tom,

Based on a given date, can you please tell me how to find 2nd Saturday of each month?

Thanks
Tom Kyte

Followup  

January 24, 2012 - 1:37 pm UTC

ops$tkyte%ORA11GR2> select dt, next_day( last_day( add_months(dt,-1) ), 'SAT' ) + 7
  2    from (select add_months(trunc(sysdate,'y')+15, level-1) dt from dual connect by level <= 12)
  3  /

DT        NEXT_DAY(
--------- ---------
16-JAN-12 14-JAN-12
16-FEB-12 11-FEB-12
16-MAR-12 10-MAR-12
16-APR-12 14-APR-12
16-MAY-12 12-MAY-12
16-JUN-12 09-JUN-12
16-JUL-12 14-JUL-12
16-AUG-12 11-AUG-12
16-SEP-12 08-SEP-12
16-OCT-12 13-OCT-12
16-NOV-12 10-NOV-12
16-DEC-12 08-DEC-12

12 rows selected.

weekends2 - function explaination

September 30, 2013 - 1:52 pm UTC

Reviewer: Rajeshwaran

Tom,

In your orginal post on the function weekends2, Can you explain why we do -4 and -5 for the l_some_add and l_some_sub calculations? I am unable to understand that part? where does -4 and -5 come from? please explain that piece.