How to Get the Week Number from a Date in SQL Server

To get the week number from a date in SQL Server, you can use DATENAME Built-in Function:

SELECT DATENAME(ww, GETDATE())

datename_week_number_getdate

SELECT DATENAME(ww, '2013-07-12 15:48:26.467')

datename_week_number_datetime

SELECT DATENAME(ww, '2011-04-17')

datename_week_number_date

The results for week number and day of the week depend on your language settings.
For example, for us_english language setting your session’s DATEFIRST setting has a default value 7 (Sunday is the first day of the week), and for German it is 1 (Monday is the first day of the week):

SET LANGUAGE us_english;
SELECT @@DATEFIRST;

SET LANGUAGE German;
SELECT @@DATEFIRST;

language_setting_datefirst

To change the DATEFIRST setting for a session without changing the language setting, use the SET DATEFIRST command.
For example, to set the first day of the week to 5 (Friday), use the following command:

SET DATEFIRST 5;