week of the month

[hisham123]

hi, can i get query to find the week of month for any given date for example date 03-aug-2007 is 1st week in the month, 10-aug-2007 is the 2nd week in the month, 19-aug-2007 is 3rd week in the month. it is enough to show between 1-5 is enough.

with regards

[azimmer]

hi, can i get query to find the week of month for any given date for example date 03-aug-2007 is 1st week in the month, 10-aug-2007 is the 2nd week in the month, 19-aug-2007 is 3rd week in the month. it is enough to show between 1-5 is enough.

with regards

There are two possibilities, I'm not sure which one you mean. If the first day of the month is mid-week (say 1st is Wednesday), is 5th in the first or in the second week by your count? If it is the second, here you have a code snippet:

declare @date as datetime
set @date = getdate()
 
declare @firstofmonth as datetime
set @firstofmonth = cast(year(@date) as varchar)+'/'+cast(month(@date) as varchar)+'/01'
print 'weekwithinmonth='+cast(datepart(wk,@date)-datepart(wk,@firstofmonth)+1 as varchar)