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)
 how many weeks in a month

Author  Topic 

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-16 : 12:54:36
Greetings

Can you please point me to the links that show how to figure out number of weeks in a specific month.
Specifically I need the following:
My first day of week is Sunday therefore if you look at
February,2009 it has 4 weeks, March, 2009 has 5 week and April 2009 it has 5 weeks and I also want the dates in the next month that fall in that week included. Even if Sunday is the last day of the current month I want the other days of the next month included.

I was trying to do this in the front end but it has some limitations using the DateDiff function.

Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2009-02-16 : 17:22:38
Usually questions in this forum get answered within minutes or within the hour. That no one has answered your question yet suggests to me that the question may not be clear to others, just as it is not clear to me.

I was with you about Sunday being the determinant of whether a week falls in a given month, and how February has 4 weeks and March has 5 weeks. But then, I lost you when you said April has 5 weeks.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-02-16 : 17:43:58
Since April only has 4 Sundays, how can it have 5 weeks?

If it does have 5 weeks, what if the first and last day of each week, and how do you define the week?




CODO ERGO SUM
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-16 : 18:19:04
Sunit et Michael. Thanks for your response, I apologize for not being clear. The days of April span 5 weeks. So my question should be how many weeks does April span?
Is that clearer?

Thank you
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-02-16 : 18:42:48
No it isn't clear, since you said that the days up until the first Sunday of April would be included in March.

Do you mean that that those days would be in both months?




CODO ERGO SUM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2009-02-16 : 18:54:40
Not quite clear to me still, but something like this?
with A as
(
   select
      dateadd(day,number,'2009-01-01') as date
   from
      master..spt_values
   where
      type = 'P' and dateadd(day,number,'2009-01-01') <= '2009-12-31'
),
B as
(
   select
      date,
      datepart(wk,date) weeknum,
      datepart(mm,date) monthnum
   from A
) 
select
   monthnum,
   max(weeknum)-min(weeknum)+1 as weekcount
from
   B
group by
   monthnum
order by
   monthnum
This just counts how many weeks a given month spans. So the number of weeks for all 12 months will add up to more than 52.
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-17 : 09:42:15
sunitabeck

Right on the money! That is exactly what I was looking for despite my not being clear, you read my mind!

Thank you very much!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 10:11:35
And so wrong...
See this result chart for different setting of SET DATEFIRST value (Header 1-7).
Month	1	2	3	4	5	6	7 
-----	--	--	--	--	--	--	--
 1	5	5	5	5	6	6	5
 2	5	5	5	5	5	5	4
 3	6	6	5	5	5	5	5
 4	5	5	5	6	5	5	5
 5	5	5	5	5	5	6	6
 6	5	6	5	5	5	5	5
 7	5	5	5	6	6	5	5
 8	6	5	5	5	5	5	6
 9	5	5	6	5	5	5	5
10	5	5	5	5	6	6	5
11	6	5	5	5	5	5	5
12	5	5	6	6	5	5	5

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 10:12:52
I believe Michael Valentine Jones asked you to define your week.
Until you do, we can never give you a correct answer.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-02-17 : 10:35:17
I don't know why I ever respond to questions about weeks.

No one is ever able to provide a clear definition of their week, and after that it's just guessing what they want.




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 11:16:25
I agree.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-17 : 12:12:38
Sorry about that...You are right , I never gave you a clear definition of the weeks I wanted. I appreciate your efforts anyways! I truly admire your passion and patience to solve the problem despite some of us not being able to clearly define what we want.
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-17 : 12:24:22
ok let me try this again. I want to find out how many weeks a certain month spans.
My week is defined as Sunday to Saturday. So for example when I look at the month of March, 2009 I see a span of 5 weeks.
1-7
8-14
15-21
22-28
29-April4
This is what I want my function or PROC to return to me.
Again thank you (and no need for DATEFIRST since that is default which is Sunday in US English, which is what I want)

therefore Sunitabeck's solution is perfect!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 13:14:44
Until somone, without your knowledge, changes the default server setting...


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-17 : 13:25:14
In that case I will use the SET DATEFIRST to Sunday (as you suggest) which will match my front end app where I will set datefirst to Sunday also.
So what do you say about the week definition? Is it clear?

Thanks Peso!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 14:17:03
Yes.
Thank you.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-17 : 15:26:52
1. Twice as fast
2. A third the number of reads
3. A tenth of the Actual Execution plan
4. Not dependant on SET DATEFIRST setting
DECLARE	@theYear SMALLINT

SET	@theYear = 2008

SELECT	theMonth,
	theLast - theFirst + 1 AS theWeeks
FROM	(
		SELECT	1 + Number AS theMonth,
			DATEDIFF(DAY, -1, DATEADD(MONTH, Number, DATEADD(YEAR, @theYear - 1900, 0))) / 7 AS theFirst,
			DATEDIFF(DAY, -1, DATEADD(MONTH, Number, DATEADD(YEAR, @theYear - 1900, 30))) / 7 AS theLast
		FROM	master..spt_values
		WHERE	Type = 'P'
			AND Number < 12
	) AS d


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-02-17 : 18:48:43
Interesting, with the overlapped weeks, then the year 2008 has 63 weeks.


Here is a slightly simplified version of Peter's code.

DECLARE	@theYear SMALLINT

SET	@theYear = 2008


SELECT	theMonth,
	theLast - theFirst + 1 AS theWeeks
FROM	(
	SELECT	1 + Number AS theMonth,
		datediff(day,-1,dateadd(month,((@theyear-1900)*12)+number,0))/7	 AS theFirst,
		datediff(day,-1,dateadd(month,((@theyear-1900)*12)+number,30))/7 AS theLast
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND Number < 12
	) AS d


Results:
theMonth    theWeeks    
----------- ----------- 
1           5
2           5
3           6
4           5
5           5
6           5
7           5
8           6
9           5
10          5
11          6
12          5

(12 row(s) affected)





CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-02-17 : 19:00:19
Does anyone want to ask what values this has in any case?

Weeks mark time

Months mark time

They are 2 different levels of measurement

Week 5 could start in January one year, and Feb another...and do you count the week that it starts in as it belongs to that month?

What if 1 day is in Jan, and 6 are in Feb? Does that mean it "belongs" to Jan?

Makes no sense to me



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-02-23 : 17:40:46
Awesome! Thank you Peso and Michael
So I have put this out into a proc. I pass it a certain date and can find out how many weeks a certain month spans depending on your start of week. This is very valuable, for me at least. I have a capacity planning tool that populates multiple charts. Users do not want to lose the capability to see the few days in a week that might fall in the previous month. I am looking at this one month at a time so the year sum is a non-issue for me.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site