How calculate the week if there are 5 weeks in one month?

Question

i have problem to calculate the week where the data just display four week. How calculate if there are five week?. i don't know how to do. Anybody know about this?.:(

Your question is very unclear, but similar ones have been asked [numerous times](http://stackoverflow.com/search?q=sql+week+number) before on this site. One common answer is not to calculate anything, but to use a [calendar table](http://stackoverflow.com/questions/2459260/calendar-table-week-number-of-month). — Pondlife, Jul 27 '12 at 12:45

Jeetendra.Sharma · Answer 1 · 2012-07-27T07:36:57.600

0

select count(datepart(dw,datecoln)) as col1 from YourTable group by datepart(dw,datecoln) having month(datecoln)=@inputMonth

if you get and any record with the value 5 in col1..

edited Jul 27 '12 at 07:36

answered Jul 27 '12 at 05:06

Jeetendra.Sharma

184
1
8

score 0 · Answer 2 · answered Jul 27 '12 at 07:46

0

Ideally we will not have 5 weeks in a month.

answered Jul 27 '12 at 07:46

AnandPhadke

13,160
5
26
33

but that does not mean its 5 weeks in that month. – AnandPhadke Jul 28 '12 at 05:13

score -1 · Answer 3 · edited May 23 '17 at 11:55

-1

Here the logic.

Calculate week number of given date

find first day of the month and also find its week number (say start)

find last day of month and also find its week number (say end)

number off week in current date = end-start

DECLARE @GivenDate datetime
DECLARE @start BIGINT
DECLARE @end BIGINT
SET @givendate=getdate()--You can supply your date

SET @start =  (SELECT DATEPART(wk,(SELECT DATEADD(mm,DATEDIFF(mm,0,@GivenDate),0))))
SET @end =  (SELECT DATEPART(wk,(SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@GivenDate)+1,0))))))

SELECT (@end-@start)+1 AS NoOfWeeks

Refer this to find week number

Or Refer this post

edited May 23 '17 at 11:55

Community

1
1

answered Jul 27 '12 at 06:27

Pintu Paul

389
3
18

This result is always 4 irrespective of any of the months..!! So I guess its not the perfect solution – AnandPhadke Jul 27 '12 at 06:59
that was a problem in substation, i corrected it. please check it – Pintu Paul Aug 06 '12 at 06:48
For september 2012 month its giving 6 weeks..!! thats not correct – AnandPhadke Aug 06 '12 at 06:55
Refer the calendar. September 1st 35nd week September 31 is 40th week So the No week should be 6. – Pintu Paul Aug 06 '12 at 11:19
The way u are calculating the week number is not correct.In a week you have 7days and based on that you need to calculate. – AnandPhadke Aug 06 '12 at 11:21
Week number is the return value of sql function (DATEPART) k,i will check. – Pintu Paul Aug 06 '12 at 11:23

How calculate the week if there are 5 weeks in one month?

3 Answers3