I have a problem with the datepart function.
I want to use datepart to get the week number but for years where January 1 is on a friday, saturday or sunday I don´t get the correct answer.
Ex. select datepart (week, '2005-01-01') returns 1, but according to my calendar it should be 53.
(the ISO 8601 standard where the first week of the year is guaranteed to have a minimum of 4 days)
Datefirst is set to 1.
What can I do to be sure that I always get the correct week number?
SQL Server 2000 sp 2