How do i use function to calculate the month period to weeks

OK, often resort to a Calendar table for this type of work,

unless it follows a pattern like 5,4,4

or, there is some other rule like 'first monday' to 'last monday'

If you can give some insight into possible rules, can give you some answers... and write the funtions...

create proc getweeks(@Mon int, @Year int)
as
begin

declare @Date datetime
set @Date = convert(datetime,CONVERT(varchar(2),@Mon)+'/1/'+ CONVERT(varchar(4),@Year))

declare @i int
set @i = 0
while @i < 5
begin
if datepart(m,DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7) = 2
select 'Week '+ convert(char(1),@i+1),convert(varchar(12),DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7),
CONVERT(varchar(12),DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7+4)
set @i = @i +1
end

end

getweeks 2,2009

I was thinking more of a function returning multi-weeks, or, a calendar table if being used for joins and in queries.

is that possible to have just 4 column with details of period in the column?

Yes...

But what about my questions above ? and one more for you, how do you want to use the results of getting weeks from providing a month ?

the function only works for 02,2009 what about other date?

I will use this function to create a chart in Crystal Report. the chart will show different catalogue of stocks sells in each week for give a month parameter.

Well, still not quite sure of how you want to use it, but now understand where you want to use it, just not how.

Do you plan to use it as part of a query ? do you plan to use it to join to a data source to get the week ? Or are you just needing a query to give you the weeks ?

e.g. Using a CTE query (and does not have to be, it is just an example) we can dynamically generate a while pile of dates - that is not the problem. The problem is more realistically what mechanism or delivery method would you prefer to use from crystal.  Examples could be a calendar table, a view, a dynamic query, a function (as in return the week commencing date with the datarows), or, are you saying you want to create a function or expression in Crystal itself ?

See below an example of a query with excrutiatingly long hand method for w/c dates regardless of first daate setting in the database, and that can be significantly tidied as well...

To run it, open a query window (say in SSMS) and paste the code then run.

;with cte_weeks as
(select 0 as weekno, 2008 as [wc_year], 01 as [wc_month], 0 as wc, case when 1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1) < 0 then dateadd(ww,1,(dateadd(dd,1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1),'20080101'))) else dateadd(dd,1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1),'20080101') end - 7 as Monday_Week_Commencing_Date, getdate() as Monday_Week_Ending_Date
 union all
 select case when wc_year = year(dateadd(dd,7,Monday_Week_Commencing_Date)) then weekno + 1 else 1 end , year(dateadd(dd,7,Monday_Week_Commencing_Date)), month(dateadd(dd,7,Monday_Week_Commencing_Date)), case when wc_month = month(dateadd(dd,7,Monday_Week_Commencing_Date)) then wc + 1 else 1 end, dateadd(dd,7,Monday_Week_Commencing_Date), dateadd(dd,14,dateadd(ms,-10,Monday_Week_Commencing_Date))
 from cte_weeks where wc_year in (2008,2009,2010) )  -- or maybe = year(dateadd(dd,7,Monday_Week_Commencing_Date)) )
select * from cte_weeks 
where weekno > 0 
--AND WC_YEAR = 2009 and WC_MONTH = 2
option (maxrecursion 0) 

Select allOpen in new window

I just need a query(store procedure) to give me the weeks when i give a month parameter. example 03 2009. the return will be week1 = 2 Mar to 6 Mar week2 = 9 Mar to 14 Mar week3 = 16 Mar to 20 Mar week4 = 23 Mar to 27 Mar and week5 30 Mar to 3 Apr. thanks for your help