【SQL】依當年度週別取得日期

--MSSQL
with ach as
(
 select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date
 from master..spt_values
 where [type] = 'p' and number between 0 and 400 
),cte as
(
 select *
 from ach
 where year(date) = year(getdate())
), abc as
(
select datepart(wk,date) as wk,min(date)  mindate,max(date)  maxdate
from cte
group by datepart(wk,date)
)


select CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), -1),112 ) as int ) as 'SUNDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 0),112 ) as int ) as 'MONDAY' 

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 1),112 ) as int ) as 'TUESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 2),112 ) as int ) as 'WENDNESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 3),112 ) as int ) as 'THURSDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 4),112 ) as int ) as 'FRIDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 5),112 ) as int ) as 'SATURDAY'
from abc
where wk='1' -- 自行代入週別