Find Week number of month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Week number of month

Author

Topic

mtcoder
Starting Member

19 Posts

Posted - 2010-05-26 : 15:16:31

I am trying to get the week number of each month. Basically
I want take a date field (note: time has been all cleared to 0:0:0) and generate the week of month it is. Our weeks start on Sunday.
The end result will be something like this:

DateField | MonthYear | WeekofMonth
1/3/2010 | 01-2010 | 1
1/4/2010 | 01-2010 | 1
.
.
1/10/2010 | 01-2010 | 2

etc.
My end goal is to use reporting services to group by the MonthYear then WeekofMonth for the data. So I can drill into the MonthYear then have each week.

I tried using datepart(wk,DateField) but I get weeks listed from 1 to 52, so its week of year instead of by each month.

Thank you

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-05-27 : 01:02:41

For WeekOfMonth can you try this-> CEILING(Day(DateField)/7)

--------------------
Rock n Roll with SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-27 : 07:50:41

Use Integer Division

SELECT (DATEPART(DAY, theDateColumn) - 1) / 7 + 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources