If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: How to get "week of the month"?

This seems like a difficult one ..

I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.

for instance let's call my function "weekOfMonth()":

weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april)
weekOfMonth(4/7/2004) should return 2 (it's the second week of april)
etc ..

This opens a very large can of worms. How do you define "week of the month" ? Do you count the 1-7, 8-14,15-21 days? Do you always start the week on a Monday? Is the week of the month related to the week of the year?

Check out the ISO pages for discussions of time and the issues related to it. They get really, REALLY ugly!

my 2 cents:
I had the problem to test something looks like "third monday of the month";
I resolved:
select * from tabella where datepart(dw,day_test)=1 and ceiling(datepart(dd,day_test)/7)=3
(I'm in Italy and here week starts on monday...)

Originally posted by zeus77
my 2 cents:
I had the problem to test something looks like "third monday of the month";
I resolved:
select * from tabella where datepart(dw,day_test)=1 and ceiling(datepart(dd,day_test)/7)=3
(I'm in Italy and here week starts on monday...)

I think that my solution works nicely if you use SET DATEFIRST, doesn't it?