Sql-server – Does the week day number changes according to localization in SQL Server

The weekday number is controlled by the SET DATEFIRST setting.

http://msdn.microsoft.com/en-us/library/ms181598.aspx

This setting is changed when the SET LANGUAGE setting is changed.

SET LANGUAGE English;
GO

SELECT 
  @@DATEFIRST as DateFirst,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
7   1999-01-01 00:00:00.0000000 6

And

SET LANGUAGE German;
GO

SELECT 
  @@DATEFIRST as DateFist,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
1   1999-01-01 00:00:00.0000000 5

You can find the DATEFIRST setting for each language by querying sys.syslanguages view and referencing the datefist column.

http://msdn.microsoft.com/en-us/library/ms190303.aspx