确定性标量函数来获取一年的一周约会(Deterministic scalar function to

下面是如何获得星期的日期，一个伟大的方式确定性标量函数来获取星期的日期 。

现在，任何人都可以帮我创建一个确定性标量函数来获取一年中每周的日期吗？ 谢谢。

Answer 1:

这工作确定性，我可以把它作为一个计算列。

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

测试代码：

;
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)

文章来源: Deterministic scalar function to get week of year for a date