用于计算当年周次
select number
,right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))) < year(getdate())
then cast(year(getdate()) as varchar)+'-1-1'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))
end,102),5) + '~' +
right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6) > year(getdate())
then cast(year(getdate()) as varchar)+'-12-31'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6
end,102),5) as date_between
from master..spt_values
where type='p'
and number < datepart(ww,cast(year(getdate()) as varchar)+'-12-31')+1
and number > 0
水平有限，不知是否可以进一步优化，还请各路高人指点
优化 sql
------解决方案--------------------
函数用的太多了，没必要用那么多吧？漫山遍野的都是红色。
------解决方案--------------------
WITH tt
AS ( SELECT DATEPART(wk,
DATEADD(dd, m.number,
DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()),
0))) AS wk ,
DATEADD(dd, m.number,
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AS dd
FROM master..spt_values m
WHERE type = 'P'
),
t1
AS ( SELECT wk ,
CONVERT(VARCHAR, DATEPART(mm, dd)) + '.'
+ CONVERT(VARCHAR, DATEPART(dd, dd)) AS cdd
FROM TT
WHERE DATEDIFF(yy, dd, GETDATE()) = 0