HEELPBOOK - Why is the week number returned wrong? (SQL Server) ############################## 

########## SCENARIO

Example:


select datepart(wk,'19990323')


returns 13 when it should be 12.

This is because SQL Server starts counting weeks from Jan 1.

Week 1 = January 1


########## SOLUTION #1

The ISO standard is that week 1 is the first week with 4 days in it.

The following code can be used (@date is the datetime) to return the ISO week:


declare @ISOweek as integer
DECLARE @date datetime
SELECT @date = getdate()
select @ISOweek= datepart(wk,@date)+1-datepart(wk,'Jan 4,'+CAST(datepart(yy,@date) as CHAR(4)))
if (@ISOweek=0)
select @ISOweek=datepart(wk, 'Dec '+ CAST(24+datepart(day,@date) as
CHAR(2))+','+CAST(datepart(yy,@date)-1 as CHAR(4)))+1
print @ISOweek



########## SOLUTION #2

Shouldn't you just look at the day of the week of January the 1st this year?

If that's friday, or later that week, then week 1 is actually week 0...

So:


DECLARE @weekday INT
DECLARE @change INT
SET @change = 0
SELECT @weekday = DATEPART(weekday, 'Jan 1 ' + CAST(DATEPART(year, Getdate()) AS CHAR(4)))
IF @weekday > 5 SET @change = -1
SELECT DATEPART(week, GETDATE()) + @change



############ ARTICLE INFO #############

Article Month: October
Article Date: 26/10/2012
Permalink: http://heelpbook.altervista.org/2012/why-is-the-week-number-returned-wrong-sql-server/
Source: http://www.windowsitpro.com/article/john-savills-windows-faqs/why-is-the-week-number-returned-by-sql-server-wrong-14340
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts