Get week number in year from date

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,

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