Count weeknum of year (Mon-Sun)

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

General SQL Server Forums

New to SQL Server Programming

Count weeknum of year (Mon-Sun)

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-18 : 14:26:49

Hi,

I am trying to get the week number of the year by using
datepart(wk,@dtdate)
But with this it is counting weeknumber =1 from Jan 1 2009 (which is a Thurs)
to Jan 3 2009 (which is a Saturday).
What I want is to count weeknumber =1 from Jan 1 (which is a Thurs) to Jan 4 2009 (which is a Sun) and then
from Jan 5 2009 (which is a Monday) to Jan 11 2009 (which is a Sunday) as Weeknumber=2..

Thanks,
Petronas

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-18 : 14:30:21

What version are you using? If you're using 2008 check out the ISO_WEEK parameter in the datepart function.

Mike
"oh, that monkey is going to pay"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-18 : 14:56:52

[code]select
[ISO_WEEK_NO], [DATE]
from
-- Date Table Function F_TABLE_DATE available on this link:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
dbo.F_TABLE_DATE ('20090101','20091231')

Results:

ISO_WEEK_NO DATE
----------- ------------------------
1 2009-01-01 00:00:00.000
1 2009-01-02 00:00:00.000
1 2009-01-03 00:00:00.000
1 2009-01-04 00:00:00.000
2 2009-01-05 00:00:00.000
2 2009-01-06 00:00:00.000
2 2009-01-07 00:00:00.000
2 2009-01-08 00:00:00.000
2 2009-01-09 00:00:00.000
2 2009-01-10 00:00:00.000
2 2009-01-11 00:00:00.000
3 2009-01-12 00:00:00.000
3 2009-01-13 00:00:00.000
...
...
51 2009-12-20 00:00:00.000
52 2009-12-21 00:00:00.000
52 2009-12-22 00:00:00.000
52 2009-12-23 00:00:00.000
52 2009-12-24 00:00:00.000
52 2009-12-25 00:00:00.000
52 2009-12-26 00:00:00.000
52 2009-12-27 00:00:00.000
53 2009-12-28 00:00:00.000
53 2009-12-29 00:00:00.000
53 2009-12-30 00:00:00.000
53 2009-12-31 00:00:00.000

[/code]

CODO ERGO SUM

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-18 : 15:04:10

Thanks for the reply..
I am using SQL server 2005..

Thanks,
Petronas

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-18 : 16:58:06

Petronas-Follow the link in Mr. Jones post (like that I called you Mr.) it should help you out

Mike
"oh, that monkey is going to pay"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-18 : 23:33:52

Hi Try this Once,

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '1/1/2009'
SELECT @EndDate = '12/31/2009'

SELECT number+1 'Day',DATEADD(DAY, number, @StartDate),DATENAME(dw,DATEADD(DAY, number, @StartDate)) AS 'WeekDay'
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources