1  CREATE OR REPLACE FUNCTION count_saturdays(begin_date IN date, end_date IN date) return number
  2  is
  3  ws_saturdays number;
  4  ws_test_date date;
  5  ws_check_date date;
  6  ws_date1 varchar(8);
  7  ws_date2 varchar(8);
  8  ws_day_of_week varchar(20);
  9  ws_loop_count number;
 10  BEGIN
 11  -- initialize counter
 12  ws_test_date := begin_date;
 13  ws_check_date := begin_date;
 14  ws_loop_count := 0;
 15  ws_saturdays := 0;
 16  ws_day_of_week := ' ';
 17  -- get saturday date for beginning week�
 18  -- select to_char(to_date(ws_test_date, 'dd-mon-yy'), 'Day')
 19  select to_char(ws_test_date, 'DAY')
 20  into ws_day_of_week
 21  from dual;
 22  --dbms_output.put_line('First Date = '||ws_test_date);
 23  --dbms_output.put_line('Day of Week = '||ws_day_of_week);
 24  if (rtrim(ws_day_of_week, ' ') = 'Saturday') then
 25  ws_saturdays := 1;
 26  end if;
 27  --dbms_output.put_line('Begin Saturdays routine - begin_date = '||begin_date|| ' end_date = '||end_date);
 28  while ws_loop_count <= 52
 29  loop
 30  -- get saturday date for beginning week�
 31  -- select next_day(trunc(to_date(ws_check_date,'dd-mmm-yy')), 'saturday')�
 32  -- select next_day(trunc(to_date(ws_check_date,'dd-mon-yy')), 'saturday')�
 33  select next_day(trunc(ws_check_date), 'saturday')
 34  into ws_test_date
 35  from dual;
 36  ws_check_date := ws_test_date;
 37  ws_date1 := to_char(ws_check_date,'yyyymmdd');
 38  ws_date2 := to_char(end_date,'yyyymmdd');
 39  -- ws_date1 := substr(to_date(ws_check_date,'yyyy-mm-dd'),1,4)||substr(to_date(ws_check_date,'yyyy-mm-dd'),6,2)||substr(to_date(ws_check_date,'yyyy- mm-dd'),9,2) ;
 40  -- ws_date2 := substr(to_date(end_date,'yyyy-mm-dd'),1,4)||substr(to_date(end_date,'yyyy-mm-dd'),6,2)||substr(to_date(end_date,'yyyy-mm-dd'),9,2) ;
 41  -- dbms_output.put_line('date1 = '||ws_date1);
 42  -- dbms_output.put_line('date2 = '||ws_date2);
 43  if (ws_date1 <= ws_date2) then
 44  ws_saturdays := ws_saturdays + 1;
 45  else
 46  ws_loop_count := 52;
 47  end if;
 48  if ws_saturdays > 52 then
 49  ws_saturdays := 52;
 50  ws_loop_count := 52;
 51  end if;
 52  ws_loop_count := ws_loop_count + 1;
 53  -- dbms_output.put_line('Counting Saturdays routine - ws_test_date = '||ws_test_date|| ' end_date = '||end_date);
 54  -- dbms_output.put_line('In Saturdays counter = '||ws_saturdays);
 55  end loop;
 56  -- dbms_output.put_line('Saturdays counter = '||ws_saturdays);
 57  -- dbms_output.put_line('Loop counter = '||ws_loop_count);
 58  return ws_saturdays;
 59* END count_saturdays;
 60  /

Function created.

SQL>