SQL Calendar

SQL Calendar

   DECLARE @Date DATETIME
Set @Date = GetDate()
DECLARE @Start DATETIME,@End DATETIME
   DECLARE @Index INT
   SET @Start = DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)
   SET @End = DATEADD(MONTH,1,@Start)
   SET @Index = DATEDIFF(DAY,-1,@Start)%7 - 1;
   SET @Start = DATEADD(mm,DATEDIFF(mm,0,@Date),0) 
   SET    @End = DATEADD(mm,1,@Start) - 1
   SET    @Index= DATEDIFF(day,0,@Start)%7
 
   ;WITH temp(date,row,col) AS
   (
       SELECT date=1,row=@Index/7+1,col=@Index%7+1
       UNION ALL
       SELECT date=date+1,row=(@Index+date)/7+1,col=(@Index+date)%7+1
       FROM temp 
       WHERE date <= DATEDIFF(DAY,@Start,@End)
   )
   SELECT    ISNULL(CONVERT(CHAR(2),[1]),'') AS 一,
           ISNULL(CONVERT(CHAR(2),[2]),'') AS 二,
           ISNULL(CONVERT(CHAR(2),[3]),'') AS 三,
           ISNULL(CONVERT(CHAR(2),[4]),'') AS 四,
           ISNULL(CONVERT(CHAR(2),[5]),'') AS 五,
           ISNULL(CONVERT(CHAR(2),[6]),'') AS 六,
           ISNULL(CONVERT(CHAR(2),[7]),'') AS 日
   FROM temp
   PIVOT
   (    
       MAX(date) FOR col IN ([1],[2],[3],[4],[5],[6],[7])
   ) AS B