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?

[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