[MSSQL]根据年月生成日历函数

 

go

--创建函数(第一版)(作者：dobear_0922)

create function fn_Calendar(@year int, @month int)

returns nvarchar(max)

as

begin

declare @result nvarchar(max), @Enter nvarchar(8)

select @Enter = char(13)+char(10), @result = ' Sun Mon Tue Wed Thu Fri Sat' + @Enter --表头

declare @start datetime, @end datetime

select @start = rtrim(@year)+'-'+rtrim(@month)+'-1', @end = dateadd(mm, 1, @start) 

set @result = @result+replicate(' ', (datepart(dw, @start)+@@datefirst+6)%7) --第一行前面的空格

while datediff(d, @start, @end)>0

begin

if (datepart(dw, @start)+@@datefirst)%7 = 1

select @result = @result+@Enter --是否换行

select @result = @result+right(' '+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start)

end

return @result

end

go

--测试示例

set datefirst 3 
print dbo.fn_Calendar(2007, 12)
select dbo.fn_Calendar(2007, 12)
set datefirst 7
--运行结果
/*

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

*/
go

--创建函数(第二版)(作者：libin_ftsafe)
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(2007,12)


--运行结果

/*

日 一 二 三 四 五 六

---- ---- ---- ---- ---- ---- ----

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 

*/