[sql server] 日历做法

http://topic.csdn.net/u/20100712/20/a14b3816-ca82-4986-b1d0-9eb16d69b005.html?seed=2094858165&r=66915049#r_66915049

 

 

declare @month as varchar(7)
set @month = '2007-12'

select 日,一,二,三,四,五,六 from
(
select week ,
  max(case weekday when 1 then datename(day,dt) else '' end ) '日',
  max(case weekday when 2 then datename(day,dt) else '' end ) '一',
  max(case weekday when 3 then datename(day,dt) else '' end ) '二',
  max(case weekday when 4 then datename(day,dt) else '' end ) '三',
  max(case weekday when 5 then datename(day,dt) else '' end ) '四',
  max(case weekday when 6 then datename(day,dt) else '' end ) '五',
  max(case weekday when 7 then datename(day,dt) else '' end ) '六'
from
(
  select week = datepart(week , m.dt) , weekday = datepart(weekday , m.dt) , dt from
  (
    select dt = @month + '-' + right('00'+cast(t.id as varchar),2) from
    (
      select 1 as id union select 2 union select 3 union select 4 union select 5
      union select 6 union select 7 union select 8 union select 9 union select 10
      union select 11 union select 12 union select 13 union select 14 union select 15
      union select 16 union select 17 union select 18 union select 19 union select 20
      union select 21 union select 22 union select 23 union select 24 union select 25
      union select 26 union select 27 union select 28 union select 29 union select 30
      union select 31
    ) t
    where isdate(@month + '-' + right('00'+cast(t.id as varchar),2)) = 1 and @month + '-' + right('00'+cast(t.id as varchar),2) <= dateadd(month , 1 , @month + '-01')
  ) m
) n
group by week
) o
/*
日  一  二  三  四  五  六                             
--  --  --  --  --  --  --
                        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                                                                                                                                                        

（所影响的行数为 6 行）
*/