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

Send Us a Sign! (Contact Us!)
--> (Word) --> (PDF) --> (Epub) --> (Text)
--> (XML) --> (OpenOffice) --> (XPS) --> (MHT)

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?

[tweet]

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

SOURCE

LINK

LANGUAGE
ENGLISH