Getting the ISO weeknumber in SQL Server 2005 (and earlier)

I just discovered that SQL Server often returns the wrong week number when using DatePart(wk, @date).
For instance, SELECT DatePart(wk, '2010-12-17') returns 47 while it should be 46.
This is because SQL Server starts counting weeks from 1 Jan, so 1 Jan is always in week 1 of the year.
The ISO standard states that week 1 is the first week with 4 days in it.
The following code can be used (with @date being the datetime) to return the ISO week

CREATE FUNCTION GetISOWeek(@date DateTime)
RETURNS INTEGER
AS
BEGIN
  declare @ISOweek INTEGER;
  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
  RETURN @ISOweek
END
GO
(From windowsitpro.com)

Since SQL2008, DatePart supports the ISO Week, by calling:
DatePart(isowk, @date) or  DatePart(isoww, @date)

Get the date of the Nth day of a given year and weeknumber

Given a year and a weeknumber, you can calculate the date of the Nth day of that week. The best way to do this as far as I have been able to come up with is this:


public static DateTime GetNthDayOfWeek(int year, int weeknumber, int n, CultureInfo culture)
{
  // Get 1st jan of given year
  DateTime date = new DateTime(year, 1, 1);


  // Get the weeknumber of this 1st jan
  int beginWeek = GetWeekNumber(date, culture);
  if (beginWeek >= 52) beginWeek = 0;


  // determine how many weeks to jump ahead for the requested week
  int weeksToProgress = weeknumber - beginWeek;


  // Add this number of weeks
  date = culture.Calendar.AddWeeks(date, weeksToProgress);


  // move to nth day and return
  int dayOfWeek = (int)culture.Calendar.GetDayOfWeek(date) - 
    (int)culture.DateTimeFormat.FirstDayOfWeek + 1;
  if(dayOfWeek <= 0) dayOfWeek += 7;
  return date.AddDays(n - dayOfWeek);
}


The GetWeekNumber method is of course the one as described in the post before this one.

Get the culture specific weeknumber in .NET

The easiest way to get a culture specific weeknumber is the following:
culture.Calendar.GetWeekOfYear(inputDate, 
    culture.DateTimeFormat.CalendarWeekRule, 
    culture.DateTimeFormat.FirstDayOfWeek);

Where culture is the culture you wish to use for determining the weeknumber. To use the current culture, you can use the following:
CultureInfo culture = CultureInfo.CurrentCulture;