In our organisation we use the ISOweek function to determine weeknumbers for a given date. This function appears to be dependant of the language setting (@@DATEFIRST) on the SQL Server. For example: January 5, 2009 should return weeknumber 2, but since the language is set to us_english it returns weeknumber 1 for the given date. Since the weeknumber returned by this function is not correct, we needed another solution.
We found another sql-function that checks the DATEFIRST setting before it calculates the weeknumbers:
This gave us the expected result.
To calculate the correct weeknumber I always used the IsoWeek function (found here: http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx).
In SQL 2008 this function is available in the DATEPART function:
SELECT
DATEPART(wk, '4 jan 2009') AS WeekNumber, DATEPART(isowk, '4 jan 2009') AS IsoWeekNumber
Output:
| WeekNumber |
IsoWeekNumber |
| 2 |
1 |