WEEK NUMBER - 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 2008 Forums

Transact-SQL (2008)

WEEK NUMBER

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-20 : 07:23:11

my input arguement is @date

@date= '2010-07-20'

now i want to display the week=4 ,because 20th july 2010 is 4th week of the month

like that i want to display week for a given date input arguement in my sp

what is query for that?

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-20 : 07:31:09

SELECT 1 + (DATEPART(DAY, @date) - 1) / 7

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 07:41:22

That gives 3 and not 4.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-20 : 09:44:23

I am not convinced OP knows himself what the rules for week calculation are.
Since he wrote no description of what constitutes a week, I assumed days 1-7 belongs to week 1, days 8-14 belongs to week 2, days 15-21 belongs to week 3, days 22-28 belongs to week 4 and 29-31 belongs to week 5.

We just have to wait for OP to confirm or post more detailed explanation of what he wants.
I can think of at least 6 ways to get the 20th of July as week #4.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-20 : 09:45:56

Also, it doesn't seem OP has the habit to revisit his topics.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147083

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-20 : 10:00:02

I hardly ever answer questions about "week number".

No one seems capable of explaining their definition of "week number", and most never reply to follow-up questions.

CODO ERGO SUM

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-21 : 01:21:46

hi all,

sun mon tue wed thu fri sat
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

this is calendar of july 2010

so 1st week is: 1-3
2nd wek is: 4-10
3rd week: 11-17
4th week: 18-24
5th week: 25-31

like that i want,

this will change for another month, for that also the answer should come as per the month

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-21 : 02:14:22

[code]
select datepart(week, DATE) - datepart(week, dateadd(month, datediff(month, 0, DATE), 0)) + 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-21 : 02:19:23

thank u very much , it works fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-21 : 05:37:41

So you basically want ISO weeknumber, but instead of going from monday to sunday, you want to go from sunday to saturday?

khtans solution will only work in some cases, because DATEPART(WEEK) is dependant on SET LANGUAGE and SET DATEFIRST settings.
If you have other setting than khtan, his suggestion may not work.

For example, how do you define the weeks of January 2010?

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

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-07-21 : 06:52:08

jan 2010 weeks

1-2 : 1st week
3-9 : 2nd week
10-16: 3rd week
17-23: 4th week
24-30 : 5thweek
31 : 6th week

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-21 : 07:38:02

for ISO WEEK, use the function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-21 : 11:54:04

This works with any setting of DATEFIRST or LANGUAGE.

select
	WeekNumber	= (datediff(dd,c.FirstWeekPriorSunday,c.DT)/7)+1 ,
	DayOfWeek	= left(datename(dw,c.DT),9),
	c.DT
from
	(
	select
		b.*,
		-- Sunday on or before first day of Month
		FirstWeekPriorSunday = dateadd(dd,(datediff(dd,-53684,b.FirstOfMonth)/7)*7,-53684)
	from
		(
		select
			a.*,
			FirstOfMonth = dateadd(mm,datediff(mm,0,a.DT),0)
		from
			( --Get Test Data
			-- Date Table Function F_TABLE_DATE:
			-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
			select DT = [DATE] from F_TABLE_DATE('20100101','20101231')  
			) a
		) b
	) c

Edit: Condensed version that produces the same result:

select
	WeekNumber	= 
	(datediff(dd,dateadd(dd,(datediff(dd,-53684,dateadd(mm,datediff(mm,0,a.DT),0))/7)*7,-53684),a.DT)/7)+1 ,
	DayOfWeek	= left(datename(dw,a.DT),9),
	a.DT
from
	( select DT = [DATE] from F_TABLE_DATE('20100101','20101231') ) a

Results:


WeekNumber  DayOfWeek DT
----------- --------- -----------------------
          1 Friday    2010-01-01 00:00:00.000
          1 Saturday  2010-01-02 00:00:00.000
          2 Sunday    2010-01-03 00:00:00.000
          2 Monday    2010-01-04 00:00:00.000
          2 Tuesday   2010-01-05 00:00:00.000
          2 Wednesday 2010-01-06 00:00:00.000
          2 Thursday  2010-01-07 00:00:00.000
          2 Friday    2010-01-08 00:00:00.000
          2 Saturday  2010-01-09 00:00:00.000
          3 Sunday    2010-01-10 00:00:00.000
          3 Monday    2010-01-11 00:00:00.000
          3 Tuesday   2010-01-12 00:00:00.000
          3 Wednesday 2010-01-13 00:00:00.000
          3 Thursday  2010-01-14 00:00:00.000
          3 Friday    2010-01-15 00:00:00.000
          3 Saturday  2010-01-16 00:00:00.000
          4 Sunday    2010-01-17 00:00:00.000
          4 Monday    2010-01-18 00:00:00.000
          4 Tuesday   2010-01-19 00:00:00.000
          4 Wednesday 2010-01-20 00:00:00.000
          4 Thursday  2010-01-21 00:00:00.000
          4 Friday    2010-01-22 00:00:00.000
          4 Saturday  2010-01-23 00:00:00.000
          5 Sunday    2010-01-24 00:00:00.000
          5 Monday    2010-01-25 00:00:00.000
          5 Tuesday   2010-01-26 00:00:00.000
          5 Wednesday 2010-01-27 00:00:00.000
          5 Thursday  2010-01-28 00:00:00.000
          5 Friday    2010-01-29 00:00:00.000
          5 Saturday  2010-01-30 00:00:00.000
          6 Sunday    2010-01-31 00:00:00.000
          1 Monday    2010-02-01 00:00:00.000
          1 Tuesday   2010-02-02 00:00:00.000
          1 Wednesday 2010-02-03 00:00:00.000
          1 Thursday  2010-02-04 00:00:00.000
          1 Friday    2010-02-05 00:00:00.000
          1 Saturday  2010-02-06 00:00:00.000
          2 Sunday    2010-02-07 00:00:00.000
          2 Monday    2010-02-08 00:00:00.000
          2 Tuesday   2010-02-09 00:00:00.000
          2 Wednesday 2010-02-10 00:00:00.000
          2 Thursday  2010-02-11 00:00:00.000
          2 Friday    2010-02-12 00:00:00.000
          2 Saturday  2010-02-13 00:00:00.000
          3 Sunday    2010-02-14 00:00:00.000
          3 Monday    2010-02-15 00:00:00.000
          3 Tuesday   2010-02-16 00:00:00.000
          3 Wednesday 2010-02-17 00:00:00.000
          3 Thursday  2010-02-18 00:00:00.000
          3 Friday    2010-02-19 00:00:00.000
          3 Saturday  2010-02-20 00:00:00.000
          4 Sunday    2010-02-21 00:00:00.000
          4 Monday    2010-02-22 00:00:00.000
          4 Tuesday   2010-02-23 00:00:00.000
          4 Wednesday 2010-02-24 00:00:00.000
          4 Thursday  2010-02-25 00:00:00.000
          4 Friday    2010-02-26 00:00:00.000
          4 Saturday  2010-02-27 00:00:00.000
          5 Sunday    2010-02-28 00:00:00.000
          1 Monday    2010-03-01 00:00:00.000
...

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-22 : 05:24:28

quote:
Originally posted by khtan

for ISO WEEK, use the function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515

KH
[spoiler]Time is always against us[/spoiler]

You can also use the DATEPART() function with ISOWEEK option since you are using SQL Server 2008.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-22 : 05:42:32

oh . . didn't notice this is a 2008 forum

KH
[spoiler]Time is always against us[/spoiler]

parody
Posting Yak Master

111 Posts

Posted - 2010-07-22 : 11:05:34

this looked like fun so thought I would have a go...

independant of datefirst options, would be much simpler otherwise of course

DECLARE @Date date
SET @Date = '20100101'

SELECT
@Date
,ceiling((DATENAME(DAY,@Date) +
CASE DATENAME(DW,CONVERT(char(6),@Date,112) + '01')
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
END)/7.0)

also isnt isoweek that is being suggested the week number of the year not week number of the month?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-22 : 11:18:58

quote:
Originally posted by parody

this looked like fun so thought I would have a go...

independant of datefirst options, would be much simpler otherwise of course

DECLARE @Date date
SET @Date = '20100101'

SELECT
@Date
,ceiling((DATENAME(DAY,@Date) +
CASE DATENAME(DW,CONVERT(char(6),@Date,112) + '01')
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
END)/7.0)

also isnt isoweek that is being suggested the week number of the year not week number of the month?

It does depend on the setting of LANGUAGE though.
Doesn't work after this statement:

set language Dutch

CODO ERGO SUM

parody
Posting Yak Master

111 Posts

Posted - 2010-07-22 : 11:26:36

aye! thats why I didnt say language independant :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources