根据给定日期2010-12-15，得到这本年本月的日历。
SUN         MON         TUE         WED         THU         FRI         SAT
----------- ----------- ----------- ----------- ----------- ----------- -----------
                                    1           2           3           4
5           6           7           8           9           10          11
12          13          14          15          16          17          18
19          20          21          22          23          24          25
26          27          28          29          30          31 

DECLARE @DATE DATETIME
SET @DATE=GETDATE()

SELECT 
SUN=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=1 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,MON=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=2 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,TUE=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=3 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,WED=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=4 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,THU=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=5 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,FRI=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=6 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
,SAT=MAX(CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))=7 THEN LTRIM(DAY(DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))) ELSE '' END)
FROM MASTER..SPT_VALUES 
WHERE TYPE='P' AND DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE))<DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@DATE),@DATE))
GROUP BY DATEPART(WEEK,DATEADD(DAY,NUMBER,DATEADD(DAY,1-DAY(@DATE),@DATE)))
/*
SUN          MON          TUE          WED          THU          FRI          SAT
------------ ------------ ------------ ------------ ------------ ------------ ------------
                                       1            2            3            4
5            6            7            8            9            10           11
12           13           14           15           16           17           18
19           20           21           22           23           24           25
26           27           28           29           30           31           
*/

------解决方案--------------------
SQL code
set datefirst 7

declare @date datetime
set @date='2010-12-15'
 
;WITH CTE AS
(
    select dd=dateadd(day,number,convert(varchar(8),@date,120)+'01'),
        DP=datepart(weekday,dateadd(day,number,convert(varchar(8),@date,120)+'01'))
    from master..spt_values
    where type='p' and number>=0
     and dateadd(day,number,convert(varchar(8),@date,120)+'01')<convert(varchar(8),dateadd(month,1,@date),120)+'01'

)
SELECT SUN,MON,TUE ,WED ,THU,FRI ,SAT
FROM 
(
    SELECT N=datepart(week,dd),
        SUN=MAX(case when DP=1 then LTRIM(day(dd)) ELSE '' end),
        MON=MAX(case when DP=2 then LTRIM(day(dd)) ELSE '' end),
        TUE=MAX(case when DP=3 then LTRIM(day(dd)) ELSE '' end),         
        WED=MAX(case when DP=4 then LTRIM(day(dd)) ELSE '' end),         
        THU=MAX(case when DP=5 then LTRIM(day(dd)) ELSE '' end),         
        FRI=MAX(case when DP=6 then LTRIM(day(dd)) ELSE '' end),         
        SAT=MAX(case when DP=7 then LTRIM(day(dd)) ELSE '' end)
    FROM CT