SQL Server 生成时间序列

WITH DateSequence AS (    
SELECT CONVERT(DATETIME,'20150101') AS DateNum    
UNION ALL    
SELECT DateNum+1    
  FROM DateSequence    
 WHERE DateNum<DATEADD(YEAR,10,CONVERT(DATETIME,'20170101'))-1    
)    
SELECT CONVERT(VARCHAR(20),ds.DateNum,112) DateNum  
     , ds.DateNum DateSequence1
     , CONVERT(DATETIME,CONVERT(VARCHAR(20),ds.DateNum,112)) DateSequence1
     , DATEADD(ww,DATEDIFF(ww,0, ds.DateNum), 0) WeekSequence  
     , DATEDIFF(ww,0, ds.DateNum)-DATEDIFF(ww,0, DATEADD(yy,DATEDIFF(yy,0, ds.DateNum), 0))+1 WeekNum1
     , DATEPART(wk,ds.DateNum) WeekNum2
     , DATEADD(mm,DATEDIFF(mm,0, ds.DateNum), 0) MonthSequence    
     , DATEADD(qq,DATEDIFF(qq,0, ds.DateNum), 0) QuarterSequence  
     , CONVERT(VARCHAR(20),YEAR(ds.DateNum))+'Q'+CONVERT(VARCHAR(20),DATEDIFF(qq,0, ds.DateNum)%4+1) QuarterName  
     , DATEADD(qq,DATEDIFF(qq,0, ds.DateNum)-DATEDIFF(qq,0, ds.DateNum)%2,0) HalfYearSequence  
     , CONVERT(VARCHAR(20),YEAR(ds.DateNum))+'H'+CONVERT(VARCHAR(20),(DATEDIFF(qq,0, ds.DateNum)%4)/2+1) HalfYearName  
     , DATEADD(yy,DATEDIFF(yy,0, ds.DateNum), 0) CurrentYearSequence    
     , YEAR(ds.DateNum) YearNum  
 FROM DateSequence ds    
OPTION(MAXRECURSION 0);