Deterministic scalar function to get the week of the year for a date

Question

Here is a great way to get the day of the week for a date. The deterministic scalar function to get the day of the week for a date .

Now, can someone help me create a deterministic scalar function to get the week of the year for a date, please? Thanks.

Answer 1

This works deterministically, I can use it as a computed column.

datediff(week, dateadd(year, datediff(year, 0, @DateValue), 0), @DateValue) + 1 

Test code:

 ; with Dates(DateValue) as ( select cast('2000-01-01' as date) union all select dateadd(day, 1, DateValue) from Dates where DateValue < '2050-01-01' ) select year(DateValue) * 10000 + month(DateValue) * 100 + day(DateValue) as DateKey, DateValue, datediff(day, dateadd(week, datediff(week, 0, DateValue), 0), DateValue) + 2 as DayOfWeek, datediff(week, dateadd(month, datediff(month, 0, DateValue), 0), DateValue) + 1 as WeekOfMonth, datediff(week, dateadd(year, datediff(year, 0, DateValue), 0), DateValue) + 1 as WeekOfYear from Dates option (maxrecursion 0)