Java SE (Java Platform, Standard Edition)

You can do this

with t
as
(
select sysdate-100 start_date, sysdate end_date
  from dual
)
select count(*)
  from (select to_char(start_date + (level-1), 'fmday') dt
          from t
        connect by level <= end_date-start_date+1)
 where dt in ('friday','saturday')

Or...

WITH some_dates AS (SELECT     TRUNC(TO_DATE(:p_date1), 'mm') + LEVEL - 1 dt
                  FROM       DUAL
                  CONNECT BY LEVEL <= :p_date1 - :p_date2)
SELECT COUNT(*)
FROM   some_dates
WHERE  TRUNC(dt) - TRUNC(dt, 'iw') IN(4,5);

Cheers

Ben

try this:

SQL> ed
Wrote file afiedt.buf

  1  SELECT TO_CHAR(dat,'DY'),COUNT(*) FROM
  2  (select TO_DATE('11-Feb-2010') + num dat from
  3  (SELECT level - 1 num
  4  FROM dual
  5  CONNECT BY level <= ABS((TO_DATE('11-Feb-2010') - TO_DATE('19-May-2010'))-1)))
  6  WHERE TO_CHAR(dat,'DY') IN ('FRI','SAT')
  7* GROUP BY TO_CHAR(dat,'DY')
SQL> /

TO_CHAR(D   COUNT(*)
--------- ----------
FRI               14
SAT               14

SQL> 

Edited by: AP on Jul 28, 2010 6:01 AM

Here goes

with t as
(
select to_date('11-FEB-2010','DD-MON-YYYY')+ level-1 dt from dual
connect by level <= to_date('19-MAY-2010','DD-MON-YYYY') - to_date('11-FEB-2010','DD-MON-YYYY')+1
)
select count(case when TRIM(to_char(dt,'DAY')) = 'FRIDAY' then dt else null end) as FRIDAY_COUNT,
 count(case when TRIM(to_char(dt,'DAY')) = 'SATURDAY' then dt else null end) as SATURDAY_COUNT
 from t;

Thanks,
Andy

select
 (next_day(to_date('20100519','yyyymmdd'),'金')-7
 -next_day(to_date('20100211','yyyymmdd')-1,'金'))/7+1
+(next_day(to_date('20100519','yyyymmdd'),'土')-7
 -next_day(to_date('20100211','yyyymmdd')-1,'土'))/7+1
as "金土の日数"
from dual;

金土の日数
----------
        28

看了看，都不错

Aketi,
could You please elaborate a little with your solutions.
It's pure magic for me .
Regards.
Greg

I am not good at English :-(
My first language is Japanese.Second language is English.

Although I am going to explain it,
I hope that SQL expert of this forum whose first language is English will explain this solution.

This solutin is using Uekisan.

I call this solution "Uekisan method" B-)
Ueki is Japanese language.

If you remember "Tabibitosan method" 1007478
Tabibitosan and Uekisan are math problems.

http://yslibrary.cool.ne.jp/sansub0301.html
http://www.manabinoba.com/index.cfm/4,1487,73,html?year=2002

In Japanese -> English dictionary,
"Ueki" means "garden tree".

My homepage uses "Uekisan method"
http://www.geocities.jp/oraclesqlpuzzle/5-18.html
http://www.geocities.jp/oraclesqlpuzzle/7-42.html

*******************************************************************************
I explain SQL logic ;-)

next_day(to_date('20100519','yyyymmdd'),'金')-7
is the most nearly Friday which is equal or less than '2010-05-19'
It is '2010-05-14'

next_day(to_date('20100211','yyyymmdd')-1,'金')
is the most nearly Friday which is equal or greater than '2010-02-11'
It is '2010-02-12'

And then please imagine two Ueki at very huge calendar.
One of Ueki exists '2010-02-12'
The another exists '2010-05-14'

And another Ueki exist between '2010-02-12' and '2010-05-14' of each Friday.

Then How many Ueki exists?
It is derived by (date '2010-05-14' - date '2010-02-12')/7+1

SQL> select count(*) fridays_and_saturdays
  2    from dual
  3   where to_char(to_date('11-Feb-2010', 'DD-MON-YYYY') + level, 'D') between 6 and 7
  4   connect by level <= TO_DATE('19-MAY-2010','DD-MON-YYYY') - TO_DATE('11-FEB-2010','DD-MON-YYYY')
  5  /

FRIDAYS_AND_SATURDAYS
---------------------
                   28

Edited by: Stepanyan Alexey on 29.07.2010 4:30

I would consider all solutions that try to build a table for all the days and then filter on that table as wrong.
Just imagine what happens when the distance between both dates is very large.
Then the connect by dual is a very slow operation. It donsn't scale very well.
There are much better ways, as Aketi already showed.

Really? And how large distance between both dates can be. Even if we take min possible date of 1/1/4712 BC and max possible date of 12/31/9999 AD the difference will be 5373484 days. And on my old laptop it takes just 3 seconds:

SQL> set timing on
SQL> select count(*) from (select level from dual connect by level <= 5373484)
  2  /

  COUNT(*)
----------
   5373484

Elapsed: 00:00:03.07
SQL> 

SY.

But 3 seconds is like ages for a database. Especially compared to a small date-Math operation which results in something like

SQL> select trunc(5373484/7)+2 from dual;

TRUNC(5373484/7)+2
------------------
            767642

Elapsed: 00:00:00.02
SQL>

I left out the data-math but so did you.

We all know scenarios where a little time spent is multiplied because this time then is spent for each row of a larger table and then it slows down heavily.

hi,
check this one


SELECT COUNT (*), TO_CHAR (date_colun_name, 'DAY')
FROM table_name WHERE RTRIM (LTRIM (TO_CHAR (date_colun_name, 'DAY'), ' ')) IN ('FRIDAY', 'SATURDAY')
and date_colun_name between strt_date and end_date
GROUP BY TO_CHAR (date_colun_name, 'DAY')

some nice coding here, I'm still amazed with what some people can do with "connect by". But I agree with some statements here that this can take "time", and to be honest, it's funny to see it working, but if you do not have a computer, just a calendar and some paper, would you go for "counting" so there must be a better solution?

The best working math in here is done by Aketi Jyuuzou, who writes so good English that I wonder why he still insists that he doesn't ;-)

Anyhow I "translated" that code to English, and I really like that math. Math is math and data is data.

ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH';

WITH my_dates AS (
SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100519','yyyymmdd') end_date FROM DUAL
UNION ALL
SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100214','yyyymmdd') end_date FROM DUAL
UNION ALL
SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100213','yyyymmdd') end_date FROM DUAL
UNION ALL
SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100212','yyyymmdd') end_date FROM DUAL
)
------
SELECT to_char(start_date,'DD.MM.YYYY') start_date,to_char(end_date,'DD.MM.YYYY') end_date,
       to_char(start_date,'DAY') start_weekday,to_char(end_date,'DAY') end_weekday,
       end_date-start_date day_difference,
       --
       (next_day(end_date,'FRIDAY')-7
       -next_day(start_date -1,'FRIDAY'))/7+1
       +(next_day(end_date,'SATURDAY')-7
       -next_day(start_date -1,'SATURDAY'))/7+1 as count_of_fr_and_sat
FROM my_dates;

START_DATE END_DATE   START_WEEKDAY                        END_WEEKDAY                          DAY_DIFFERENCE         COUNT_OF_FR_AND_SAT    
---------- ---------- ------------------------------------ ------------------------------------ ---------------------- ---------------------- 
11.02.2010 19.05.2010 THURSDAY                             WEDNESDAY                            97                     28                     
11.02.2010 14.02.2010 THURSDAY                             SUNDAY                               3                      2                      
11.02.2010 13.02.2010 THURSDAY                             SATURDAY                             2                      2                      
11.02.2010 12.02.2010 THURSDAY                             FRIDAY                               1                      1                      

-- andy