# Thread: How to get "week of the month"?

1. Registered User
Join Date
Mar 2004
Location
L.A
Posts
19

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

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322

Code:
```DECLARE @x datetime, @y datetime, @z datetime
SELECT @x = GetDate()
SELECT @y = CONVERT(datetime,CONVERT(varchar(2),DATEPART(m,@x))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@x)))
+'/01/'+CONVERT(varchar(4),DATEPART(yy,@x)))))

SELECT (DATEPART(wk,@z)-1)-DATEPART(wk,@y)```

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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!

For a rude, crude, and rather simplistic solution, you could use:
PHP Code:
``` CREATE FUNCTION dbo.f_WeekOfMonth(    @d DATETIME )  RETURNS INT AS    BEGIN       RETURN 1 + DateDiff(week, Convert(CHAR(8), @d, 121) + '01', @d)    END GO  ```
-PatP
Last edited by Pat Phelan; 04-19-04 at 13:15.

4. Registered User
Join Date
Mar 2004
Location
L.A
Posts
19
Brett,
your solution returns 3 for today's date (4/19/2004)

Pat,
Your solution seems to work fine with the examples that I tried ...

For this function, I am considering Monday as the first day of the week
and Sunday the last day ..

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Gotta tweak it...but isn't this the 3rd week?

My Calendar says so

But Datepart(wk, '4/30/2004') gives me 19, whereas my calendar say 17

What gives....

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Huh?

Code:
```SELECT COUNT(DISTINCT week_no) FROM (
select datepart(wk,'4/1/2004') as week_no UNION ALL
select datepart(wk,'4/5/2004') as week_no UNION ALL
select datepart(wk,'4/12/2004') as week_no UNION ALL
select datepart(wk,'4/19/2004') as week_no UNION ALL
select datepart(wk,'4/26/2004') as week_no UNION ALL
select datepart(wk,'4/30/2004') as week_no)AS XXX```

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
ISO standard weeks start on Sunday, and the year usually starts with week 1 (there are some funky rules there).

-PatP

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Anyone want to share what week # they think this is?

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Looking for a much bigger can now, are we?

Ok, which week in whose calendar, using which of the available standards? ISO says it is week 17 of year 2004. Unix says it is week 1791. Shall we go on?

-PatP

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally posted by Pat Phelan
Looking for a much bigger can now, are we?

Ok, which week in whose calendar, using which of the available standards? ISO says it is week 17 of year 2004. Unix says it is week 1791. Shall we go on?

-PatP

I actually meant 1-4 (or 5)

ISO say 17, my calendar says 16...

But it's still the third week...no?

11. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Code:
```CREATE FUNCTION dbo.udf_WeekOfMonth(
@d DATETIME
)  RETURNS INT AS
BEGIN
DECLARE @y datetime
SELECT @y = CONVERT(datetime,CONVERT(varchar(2),DATEPART(m,@d))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@d)))
RETURN DATEPART(wk,@d)-DATEPART(wk,@y)
END
GO

SELECT dbo.udf_WeekOfMonth('4/19/2004')
SELECT dbo.udf_WeekOfMonth('6/14/2004')```

12. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
--Depending upon what you want...

--Calendar week of the month:
select datediff(week, convert(char(7), Getdate(), 120)+ '-01', Getdate())+1

--Weeks since start of month:
select (datediff(day, convert(char(7), Getdate(), 120)+ '-01', Getdate())/7)+1

13. Registered User
Join Date
Mar 2004
Location
Venice,Italy
Posts
20
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...)

14. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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?

-PatP

15. Registered User
Join Date
Mar 2004
Location
L.A
Posts
19
Pat, your solution (the same as blindman's) works perfect ..

Brett's solution returns 3 for the '04/19/2004' ... it should be 4 because I am looking for the number of calendar week ..

Thank you guys ..

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•