Database
Fan

Number of weeks in a given month and year

Hi,
How can I find out number of weeks in any given month and year?

Please let me know.

Thanks.
4 REPLIES
Enthusiast

Re: Number of weeks in a given month and year

If you want to count the number of actual weeks that were in a month (i.e if the first of a month fall on wednesday this is considered a week of the month even though it did not have the whole 7 days in this month, similarly if the last day of the month falls on a wednesday it is considered as a week in the month) then: This is how you can calculate the weeks in a month.
select
case when day_of_week <> 1 then z.weeks_val +1
else z.weeks_val end no_of_weeks
from sys_calendar.calendar a, (select month_of_year, year_of_calendar, max(week_of_month)
from sys_calendar.calendar
group by 1,2) z(month_val, year_val, weeks_val)
where a.month_of_year = z.month_val
and a.year_of_calendar = z.year_val
and a.month_of_year = 1
and a.year_of_calendar = 2006
and a.day_of_month = 1

but if you take first 7 days of the month to be one week and so on ... I guess this is how you can get number of weeks in a month.

select max(day_of_month)/7 ||'weeks'||max(day_of_month) mod 7 ||'days'
from sys_calendar.calendar
where month_of_year = 1
and year_of_calendar = 2006

I am not sur if this is what you are looking for.
I am pretty sure our GURUS will comeup with better solution.

Enthusiast

Re: Number of weeks in a given month and year

Abdul,

You can use the following

SEL
Distinct
Year_Of_Calendar (TITLE 'YEAR')
,Month_Of_Year (TITLE 'MONTH')
,Week_Of_Month + 1 (TITLE 'WEEK')
--,Calendar_Date (TITLE 'DATE')
FROM sys_calendar.calendar
WHERE year_of_calendar = 2006
AND month_of_year = 3
ORDER BY 3;

Optionally you can uncomment the 4th projected column to get the date.

Vinay
Enthusiast

Re: Number of weeks in a given month and year

hello Vinay!

SEL
Distinct
Year_Of_Calendar (TITLE 'YEAR')
,Month_Of_Year (TITLE 'MONTH')
,Week_Of_Month + 1 (TITLE 'WEEK')
--,Calendar_Date (TITLE 'DATE')
FROM sys_calendar.calendar
WHERE year_of_calendar = 2006
AND month_of_year = 3
ORDER BY 3;

This query will give some errors.
Try this for the month January or october the week will start with 2 instead of 1
FOR JANUARY 2006
SEL
Distinct
Year_Of_Calendar (TITLE 'YEAR')
,Month_Of_Year (TITLE 'MONTH')
,Week_Of_Month + 1 (TITLE 'WEEK')
--,Calendar_Date (TITLE 'DATE')
FROM sys_calendar.calendar
WHERE year_of_calendar = 2006
AND month_of_year = 1
ORDER BY 3;
YEAR MONTH WEEK
2006 1 2
2006 1 3
2006 1 4
2006 1 5
2006 1 6

we get the similar results for october too.

The calendar table has a funny way of determining the week of the month. If a week starts on the last month and it continues into the current month the value of the week_of_month is 0 (zero)
if the first day of the month starts on a sunday then the week_of_month starts with 1.
That was the reason why I was checking for the start day of the month and then taking either 1 or adding 1 to the week_of_month.

Thanks

Enthusiast

Re: Number of weeks in a given month and year

Feroz,

Good catch.
I should have checked for other months as well.

Vinay