生成日历

CREATE PROCEDURE [dbo].[GetCalendar]
    (
      @InputDateTime DATE = NULL
    )
AS 
    BEGIN
        IF @InputDateTime IS NULL 
            SET @InputDateTime = GETDATE() ;

        WITH    x ( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Week, Date )
                  AS ( SELECT   CASE WHEN DATEPART(dw, thismonth.first) = 1
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 2
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 3
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 4
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 5
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 6
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, thismonth.first) = 7
                                     THEN DAY(thismonth.FIRST)
                                     ELSE NULL
                                END ,
                                DATEPART(WK, thismonth.first) ,
                                thismonth.first
                       FROM     ( SELECT    DATEADD(D,
                                                    -DATEPART(d,
                                                              @InputDateTime)
                                                    + 1, @InputDateTime) AS first
                                ) AS thismonth
                       UNION ALL
                       SELECT   CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 1
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 2
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 3
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 4
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 5
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 6
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 7
                                     THEN DAY(DATEADD(d, 1, x.Date))
                                     ELSE NULL
                                END ,
                                DATEPART(WK, DATEADD(d, 1, x.Date)) ,
                                DATEADD(d, 1, x.Date)
                       FROM     x
                       WHERE    MONTH(DATEADD(d, 1, x.Date)) = MONTH(x.Date)
                     )
            SELECT  MAX(x.Sunday) AS Sunday ,
                    MAX(x.Monday) AS Monday ,
                    MAX(x.Tuesday) AS Tuesday ,
                    MAX(x.Wednesday) AS Wednesday ,
                    MAX(x.Thursday) AS Thursday ,
                    MAX(x.Friday) AS Friday ,
                    MAX(x.Saturday) AS Saturday
            FROM    x
            GROUP BY Week
            ORDER BY Week
    END    

GO