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).

CREATE FUNCTION dbo.udf_GetISOWeekdayNumberFromDate(@dtDate AS DATETIME) RETURNS INT WITH RETURNS NULL ON NULL INPUT
/*
** Return the ISO week day number (1 through 7 for Monday through Sunday)
**    regardless of the DATEFIRST session setting.
*/
AS
BEGIN

   RETURN (((DATEPART(dw, @dtDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1

END
GO

-- Tests / Examples
SET DATEFIRST 1 -- First day of week is Monday.
SELECT
	dbo.udf_GetISOWeekdayNumberFromDate('1753-01-01') AS MinDate,
	dbo.udf_GetISOWeekdayNumberFromDate('9999-12-31') AS MaxDate,
	dbo.udf_GetISOWeekdayNumberFromDate('2000-01-01') AS NewMillenium
SET DATEFIRST 7 -- First day of week is Sunday.
SELECT
	dbo.udf_GetISOWeekdayNumberFromDate('1753-01-01') AS MinDate,
	dbo.udf_GetISOWeekdayNumberFromDate('9999-12-31') AS MaxDate,
	dbo.udf_GetISOWeekdayNumberFromDate('2000-01-01') AS NewMillenium
GO