You probably need to specify the first day of your week with set datefirst :

set datefirst 1;
select datepart(week, '2017-02-01');

returns 6

Depending on the default language, your datefirst might be set to 7 .

set datefirst 7;
select datepart(week, '2017-02-01');

returns 5

rextester demo: http://rextester.com/KEPB52852

You can also consider using 'iso_week' instead of 'week' parameter in 'datepart'. This case you can avoid using 'set datefirst 1', which can be convenient if you can only use a single select.

More details here about 'iso_week': "ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occur"

You can compare the two like this:

SELECT datepart(ISO_WEEK, '2020.01.01') -- Wed
SELECT datepart(WEEK, '2020.01.01') -- Wed
SELECT datepart(ISO_WEEK, '2020.01.05') -- Sun
SELECT datepart(WEEK, '2020.01.05') -- Sun
SELECT datepart(ISO_WEEK, '2020.01.06') -- Mon
SELECT datepart(WEEK, '2020.01.06') -- Mon

Note the difference for Sunday, 5 Jan 2020:

-----------
1
1
1
2
2
2

You can get the week number from a specific column which contains a date by using:

DATEPART(WK, orders.createddate) AS Week,

This is taken from the DATEPART functions available below:

DATEPART(YY, orders.createddate) AS Year, 
   DATEPART(QQ, orders.createddate) AS Quarter, 
   DATEPART(WK, orders.createddate) AS Week, 
   DATEPART(DY, orders.createddate) AS dayofYear, 
   DATEPART(MM, orders.createddate) AS Month, 
   DATEPART(DD, orders.createddate) AS Date, 
   DATEPART(hour, orders.createddate) AS Hour, 
   DATEPART(minute, orders.createddate) AS Minute, 
   DATEPART(second, orders.createddate) AS Second, 
   DATEPART(millisecond, orders.createddate) AS Millsecond, 
   DATEPART(microsecond, orders.createddate) AS Microsecond, 
   DATEPART(nanosecond, orders.createddate) AS Nanosecond,

I know this is very old, but it might help somebody. If you mean you want the exact number of week in some given date, then you might use this query:

SELECT WEEK("2022-05-29"); --> 22

resource: https://www.w3schools.com/sql/func_mysql_week.asp

For week starting on Monday, without using "set datefirst":

select datepart(iso_week, '2023-03-22')

Because in a SQL view you cannot set datefirst which is a session variable. I found the right documentation:

ISO Week Number You can also return the ISO week number from a date by using the iso_week argument. ISO weeks start on Mondays and the first week of a year contains January 4 of that year.