Calendar--显示某个月

第一种方法:

create function f_calendar(@year int,@month int)
returns @t table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))
as
begin
declare @a table(id int identity(0,1),date datetime)
insert into @a(date) select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects
update @a set date=dateadd(dd,id,date)
insert into @t
select
max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end)
from @a
where month(date)=@month
group by (case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end)
return
end
go
set datefirst 1
select * from dbo.f_calendar(2009,9)
go
drop function f_calendar
go

第二种方法:

with x(dy,dm,mth,dw,wk)
as(
select dy,day(dy) dm,datepart(m,dy) mth,datepart(dw,dy) dw,
case when datepart(dw,dy)=1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk
from ( select dateadd(day,1-day(getdate()),getdate()) dy )a
union all
select dateadd(day,1,dy),day(dateadd(day,1,dy)),mth,datepart(dw,dateadd(day,1,dy)),
case when datepart(dw,dateadd(day,1,dy))=1 then datepart(ww,dateadd(day,1,dy))-1 else datepart(ww,dateadd(day,1,dy)) end wk
from x
where datepart(m,dateadd(day,1,dy))=mth )

select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from x
group by wk
order by wk
第三种方法:

declare @month as varchar(7)
set @month = '2009-10'
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)x
第四种方法:

if object_id('tempdb.dbo.#tb') is not null drop table #tb
set nocount on
create table #tb (dt varchar(10),wd nvarchar(10))
declare @date datetime,@m varchar(10),@nextm varchar(10),@judge varchar(10)
set @date = getdate()--可以换成任意一天
set @m = convert(varchar(8),@date,120)+'01'
set @nextm = convert(varchar(10),dateadd(m,1, @m),120)
set @judge = @m
while cast(@judge as datetime)<cast(@nextm as datetime)
begin
insert into #tb select @judge ,datename(weekday,@judge)
set @judge=convert(varchar(10),dateadd(day,1, @judge),120)
end
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+' max(isnull(case wd when '''+b.wd+''' then right(dt,2) end,'''')) as '''+b.wd+''''
from ( select top 100 percent * from (select distinct wd from #tb) a
order by case a.wd
when datename(weekday,0) then 1
when datename(weekday,1) then 2
when datename(weekday,2) then 3
when datename(weekday,3) then 4
when datename(weekday,4) then 5
when datename(weekday,5) then 6
when datename(weekday,6) then 0 end) b
exec ('select '+@sql+'from #tb group by datepart(week,dt) order by datepart(week,dt)')
set nocount off