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.