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

for instance
Feb 2009 weeks equal week1 = 2 Feb to 6 Feb, Week2 = 9 Feb to 13 Feb  week3 = 16 to 20 and week4 =  23 Feb to 27 Feb  

March 2009 weeks equal 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

when i give a month parameter. the function should return weeks with date period.
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, that is very close to what emes already gave you....  you just call it with what ever period you want e.g. getweeks 3,2009  or getweeks 1,2009

But, anyway, here is one for you

-- step 1 -- a once-off task to create the stored procedure. Once there use as often as you like. two parameters first month then year
create proc usp_week_labels(@mth INT,@yr INT)
declare @dt datetime
declare @wk1 varchar(20)
declare @wk2 varchar(20)
declare @wk3 varchar(20)
declare @wk4 varchar(20)
declare @wk5 varchar(20)
if @mth between 1 and 13 and @yr > 2000 
   set @dt = convert(datetime, (convert(varchar,@yr) + right('00'+convert(varchar,@mth),2) + '01'), 112)
   set @dt = case when 1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)) < 0 then dateadd(ww,1,(dateadd(dd,1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)),@dt))) else dateadd(dd,1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)),@dt) end
   set @wk1 = convert(varchar(6),@dt,106) + ' to ' + convert(varchar(6),dateadd(dd,5,@dt),106)
   set @wk2 = convert(varchar(6),dateadd(dd,07,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,07,@dt)),106)
   set @wk3 = convert(varchar(6),dateadd(dd,14,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,14,@dt)),106)
   set @wk4 = convert(varchar(6),dateadd(dd,21,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,21,@dt)),106)
   if month(dateadd(dd,28,@dt)) = @mth set @wk5 = convert(varchar(6),dateadd(dd,28,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,28,@dt)),106) else set @wk5 = ''
select @wk1 as Week1,@wk2 as Week2,@wk3 as Week3,@wk4 as Week4,@wk5 as Week5
-- step 2 is to simply use it two examples below, one for feb and a more formal syntax of running for march, and the most formal syntax for April (all the same thing just different months)
usp_week_labels 2,2009
-- slightly more formal syntax
exec ..dbo.usp_week_labels 3,2009
-- absolute most formal syntax for running a stored procedure
execute <dbname>.dbo.usp_week_labels @mth=4,@yr=2009                            -- replace <dbname> with your actual database name

Open in new window

Mark WillsTopic AdvisorCommented:
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)

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
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


getweeks 2,2009
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mark WillsTopic AdvisorCommented:
I was thinking more of a function returning multi-weeks, or, a calendar table if being used for joins and in queries.
wanlijunAuthor Commented:
is that possible to have just 4 column with details of period in the column?
Mark WillsTopic AdvisorCommented:

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 ?
wanlijunAuthor Commented:
the function only works for 02,2009 what about other date?
wanlijunAuthor Commented:
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.
Mark WillsTopic AdvisorCommented:
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) 

Open in new window

wanlijunAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.