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.