ISO 8601 defines a week as a period of seven days beginning with a Monday. The days of the week are numbered starting with 1. See the ISO 8601 entry in Wikipedia for more info. The SQL Server DATEPART function can be used to get the number of the week day but the result is dependant on the setting of the DATEFIRST session setting, which by default sets Sunday to be the first day of the week.
The function below returns the ISO week day number for the supplied date regardless of the DATEFIRST setting. In set based operations it may be preferable to inline the expression for performance reasons. I have a separate function for the ISO Week Number.
This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option).