March 15, 2001 - 7:57 am UTC
Reviewer: Helena Markova from Bratislava, Slovakia
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.
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;
WHILE count_date <= max_date LOOP
IF TO_CHAR(count_date,'DY') IN ('SAT','SUN') THEN
no_of_ekend := no_of_wkend+1;
count_date := count_date+1;
April 08, 2001 - 2:28 pm UTC
Reviewer: Gautam from BC, Canada
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
) RETURN NUMBER IS
p_count number := 0;
tdate date ;
tdate := p_start_date;
if tdate > p_end_date then
x := to_char(tdate,'DAY');
if rtrim(x) in ('SATURDAY','SUNDAY') then
p_count := p_count +1;
tdate := tdate + 1;
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
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
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
into m_var,m_var2 from dual;
if m_var in('sat','sun') then
insert into week_end values(m_var,m_var2);
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.
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 */
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;
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
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)
select to_char(:mydate + rownum,'dy') r1, rownum as rr
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?
March 14, 2003 - 5:46 pm UTC
You must have an awful slow system:
2 mydate date := sysdate;
3 x date;
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
11 where t.r1 ='sat';
PL/SQL procedure successfully completed.
But anyway -- look at next_day
ops$tkyte@ORA920> select next_day( sysdate, 'sat' ) from dual;
September 13, 2006 - 8:20 am UTC
Reviewer: A reader
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
Based on a given date, can you please tell me how to find 2nd Saturday of each month?
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)
12 rows selected.
weekends2 - function explaination
September 30, 2013 - 1:52 pm UTC
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.