Count of Mondays in a Month

[thermidor]

Hi All,

Can anyone suggest a query that will tell me how many times a given weekday occurs within a month. I want to know that in Jul 07 there are 5 Mondays; in Aug there are 4 Mondays; etc.

TIA,
Sven

 

[SantaMufasa]

Thermidor,

Although you could resolve your need entirely in SQL, it seems to me that a user-defined function would be tigher on an on-going basis:

create or replace function Count_DoW (date_in date, Day_of_Week varchar2) return number is	
begin
        if date_in is null then return null;
        end if;
        If to_char(next_day(trunc(date_in,'MM')-1
                  ,substr(Day_of_week,1,3))+21,'DD')
           <= to_char(last_day(date_in),'DD')-7 then
            return 5;
        else
            return 4;
        end if;
end;
/

Function created.

Here is its invocation:

clear col
col a heading "Number|of Mondays|in July"
col b heading "Number|of Mondays|in August"
select count_dow(sysdate,'MON') a, count_dow(sysdate+30,'MON') b
  from dual;

    Number     Number
of Mondays of Mondays
   in July  in August
---------- ----------
         5          4

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]