Get Week Number in Year from Date

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,