A little more code in this function than the week number of month function. You can substitute the beginning quarter dates for something custom...like an Accounting Period for instance.
declare @p_date smalldatetime
set @p_date = '5/15/2007' --or any date you likeselect CASE
WHEN datepart(mm, @p_Date) >=1 and datepart(mm, @p_Date) <=3 THEN
datediff(wk, cast('01/01/' + cast(datepart(yyyy,@p_date) as nvarchar(4)) as smalldatetime),@p_date) +1
WHEN datepart(mm, @p_Date) >=4 and datepart(mm, @p_Date)<=6 THEN
datediff(wk, cast('04/01/' + cast(datepart(yyyy,@p_date) as nvarchar(4)) as smalldatetime),@p_date) +1
WHEN datepart(mm, @p_Date)>=7 and datepart(mm, @p_Date)<= 9 THEN
datediff(wk, cast('07/01/' + cast(datepart(yyyy,@p_date) as nvarchar(4)) as smalldatetime),@p_date) +1
ELSE
datediff(wk, cast('10/01/' + cast(datepart(yyyy,@p_date) as nvarchar(4)) as smalldatetime),@p_date) +1
END as WeekNumofQtr
In this example, the result would be 7.
