Week numbers in MS SQL Server

How to get the week number from a date

To get the ISO week number (1-53) from a date in the column datecol, use SELECT DATEPART(isoww, datecol) FROM ….

Read more about DATEPART() in the Transact-SQL manual.

How to get the number of weeks in a year

To get the number of ISO weeks (i.e. the number of the last week) in a year, get the week number of 28 December in that year using the above logic, i.e. SELECT DATEPART(isoww, DATETIMEFROMPARTS(yearcol, 12, 28, 0, 0, 0, 0)) FROM ….

This is based on the fact that the last week of the year always includes 28 December.