资源描述:
《SQL根据年月生成日历函数.docx》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、go--创建函数(第一版)(作者:dobear_0922)create function fn_Calendar(@year int, @month int)returns nvarchar(max)asbegin declare @result nvarchar(max), @Enter nvarchar(8) select @Enter = char(13)+char(10), @result = 'SunMonTueWedThuFriSat' + @Enter --表头 declar
2、e @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
3、 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 @resultend go--测试示例set datefirst
4、3print dbo.fn_Calendar(2007, 12)select dbo.fn_Calendar(2007, 12)set datefirst 7 --运行结果/* SunMonTueWedThuFriSat 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
5、*/ 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))asbegin declare @a table(id int identity(0,1),date datetime) insert in
6、to @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
7、,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(da
8、y(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