Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database abdys 3 posts Joined 07/04 17 Apr 2006 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. shaik.fe...alif.com 122 posts Joined 01/06 17 Apr 2006 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_weeksfrom 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_valand a.year_of_calendar = z.year_valand a.month_of_year = 1and a.year_of_calendar = 2006and a.day_of_month = 1but 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 = 1and year_of_calendar = 2006I am not sur if this is what you are looking for.I am pretty sure our GURUS will comeup with better solution. BBR2 96 posts Joined 12/04 19 Apr 2006 Abdul,You can use the following SELDistinct 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 = 3ORDER BY 3;Optionally you can uncomment the 4th projected column to get the date.Vinay shaik.fe...alif.com 122 posts Joined 01/06 19 Apr 2006 hello Vinay!SELDistinct 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 = 3ORDER BY 3;This query will give some errors.Try this for the month January or october the week will start with 2 instead of 1FOR JANUARY 2006SELDistinct 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 = 1ORDER BY 3; YEAR MONTH WEEK 2006 1 2 2006 1 3 2006 1 4 2006 1 5 2006 1 6we 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 BBR2 96 posts Joined 12/04 19 Apr 2006 Feroz,Good catch.I should have checked for other months as well.Vinay You must sign in to leave a comment. Active Posters