Week of the 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)

Week of the Month

Author

Topic

OmegaPrime
Starting Member

4 Posts

Posted - 2010-05-03 : 14:52:48

Ok, I found a post from a couple of years ago, but the poster didnt give enough information. Here is what I am looking for. I want a week to be 7 consecutive days, Monday - Sunday. So in the instance of this month (May 2010), May first would have been counted in the last week of April, also June 1 - 6 would be counted in the week number of the 31st. Any help would be greatly appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-03 : 15:31:51

Visakh or Peso will post in a few minutes on how to do this best, so stick around a bit

Jim

declare @date datetime
set @date = '05/01/2010'
select datepart(ww,(dateadd(ww,datediff(day,'01/01/1753',@date-7)/7,'01/01/1753')))

Jim

Everyday I learn something that somebody else already knew

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-03 : 17:02:06

Date Table Function F_TABLE_DATE contains several columns that you could use to get this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

START_OF_WEEK_STARTING_MON_DATE
First Day of Week starting Monday that DATE is in

END_OF_WEEK_STARTING_MON_DATE
Last Day of Week starting Monday that DATE is in

WEEK_STARTING_MON_SEQ_NO
Sequential Week number as offset from Week starting Monday, 1753/01/01

ISO_YEAR_WEEK_NO
ISO 8601 year and week in format YYYYWW, Example = 200403

ISO_WEEK_NO
ISO 8601 week of year in format WW, Example = 52

CODO ERGO SUM

OmegaPrime
Starting Member

4 Posts

Posted - 2010-05-03 : 17:39:55

Thats getting me close, works great for the end of the month since I can check for when the week started, and then pull the week number for that, where I am still having issues is the next week it one to high. For example, using the week start method, I get what I want that 4/29 - 5/4 is week 5 of April, however 5/5 - 5/11 is coming up as week two, where I want week one. Any suggestion?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-03 : 17:56:35

quote:
Originally posted by OmegaPrime

Thats getting me close, works great for the end of the month since I can check for when the week started, and then pull the week number for that, where I am still having issues is the next week it one to high. For example, using the week start method, I get what I want that 4/29 - 5/4 is week 5 of April, however 5/5 - 5/11 is coming up as week two, where I want week one. Any suggestion?

You could do a better job of explaining the problem, and include examples of exactly what you are after.

Also, it is fairly hard for anyone here to offer suggestions on how to fix code that you didn't post.

CODO ERGO SUM

OmegaPrime
Starting Member

4 Posts

Posted - 2010-05-03 : 18:10:25

I would post if I had something but I was completely lost.

What I am looking for is this. Week of the month (Monday - Sunday) and if the week rolls into the next month, those days rolling over would be included in the week count for the end of the pervious month. Then the next full week being the first week of that month, I'll use April and May as an example.

March 29th - April 4th (week 5 of March)
April 5th - April 11th (week 1 of April)
April 12th - April 18th (week 2 of April)
April 19th - April 25 (week 3 of April)
April 26th - May 2nd (week 4 of April)
May 3rd - May 9th (week 1 of May)

That make more sense?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-04 : 12:12:55

You could use this method:
1. Find the Monday on or before the Date you are interested in.
2. Find the seventh day of the month that the Monday from step one is in.
3. Find the first Monday of that month by finding the Monday on or before the seventh day of the month.
4. Find the difference in days between the first Monday of the Month and the Date, divide it by 7, and then add 1 to give you the week of the month.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-04 : 12:19:25

So, basically, the first week of a month is the first seven-day period starting with monday and ending with sunday?

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

OmegaPrime
Starting Member

4 Posts

Posted - 2010-05-04 : 17:09:23

Peso, that is exactly what I am looking for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources