SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO /**//****** 对象: 用户定义的函数 dbo.calander 脚本日期: 2005-12-2 22:33:20 ******/ /**//* 功能:生成日历 输入:@month,月(如1~12之间的数据),@year,年(如:2005) 返回: --以下迭代记录创建和修改信息 作者:chenzh 时间:2005-11-8 说明: */ CREATEfunction calander(@monthint,@yearint) returns@DAY1table(SUN char(3),MON Char(3),TUE Char(3),WED Char(3),THU Char(3),FRI Char(3),SAT Char(3)) as begin declare@iint declare@jint declare@intchkint declare@dnumint declare@curdateint declare@month1char(2) declare@year1char(4) declare@datechar(2) declare@dtchkint declare@dtvalint set@date='01' set@month1=@month set@year1=@year set@dtchk=1 set@i=1 set@j=1 declare@DAY2table(SUN char(3) default'',MON Char(3)default'',TUE Char(3)default'',WED Char(3)default'',THU Char(3)default'',FRI Char(3)default'',SAT Char(3)default'') SELECT@curdate=DATEPART(dw, CONVERT(DATETIME,@date+'-'+@month1+'-'+@year1,103)) select@dnum=datediff(dd,convert(datetime,@date+'-'+@month1+'-'+@year1,103),dateadd(mm,1,convert(datetime,@date+'-'+@month1+'-'+@year1,103))) while@j<=7 begin if@curdate=@j begin if@j=1 begin INSERTINTO@DAY2(sun)VALUES(@i) set@intchk=1 set@dtchk=1 end elseif@j=2 begin INSERTINTO@DAY2(MON)VALUES(@i) set@intchk=2 set@dtchk=1 end elseif@j=3 begin INSERTINTO@DAY2(TUE)VALUES(@i) set@intchk=3 set@dtchk=1 end elseif@j=4 begin INSERTINTO@DAY2(WED)VALUES(@i) set@intchk=4 set@dtchk=1 end elseif@j=5 begin INSERTINTO@DAY2(THU)VALUES(@i) set@intchk=5 set@dtchk=1 end elseif@j=6 begin INSERTINTO@DAY2(FRI)VALUES(@i) set@intchk=6 set@dtchk=1 end elseif@j=7 begin INSERTINTO@DAY2(SAT)VALUES(@i) set@intchk=7 set@dtchk=1 end end set@j=@j+1 end if@intchk=1 begin update@day2set mon=@i+1,tue=@i+2,wed=@i+3,thu=@i+4,fri=@i+5,sat=@i+6where sun=1 set@dtchk=@dtchk+6 end elseif@intchk=2 begin update@day2set tue=@i+1,wed=@i+2,thu=@i+3,fri=@i+4,sat=@i+5where mon=1 set@dtchk=@dtchk+5 end elseif@intchk=3 begin update@day2set wed=@i+1,thu=@i+2,fri=@i+3,sat=@i+4where tue=1 set@dtchk=@dtchk+4 end elseif@intchk=4 begin update@day2set thu=@i+1,fri=@i+2,sat=@i+3where wed=1 set@dtchk=@dtchk+3 end elseif@intchk=5 begin update@day2set fri=@i+1,sat=@i+2where thu=1 set@dtchk=@dtchk+2 end elseif@intchk=6 begin update@day2set sat=@i+1where fri=1 set@dtchk=@dtchk+1 end elseif@intchk=7 begin Set@dtchk=@dtchk end insertinto@day2(sun)values(@dtchk+1) set@dtchk=@dtchk+1 if@intchk=1 begin update@day2set mon=@i+8,tue=@i+9,wed=@i+10,thu=@i+11,fri=@i+12,sat=@i+13where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=2 begin update@day2set mon=@i+7,tue=@i+8,wed=@i+9,thu=@i+10,fri=@i+11,sat=@i+12where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=3 begin update@day2set mon=@i+6,tue=@i+7,wed=@i+8,thu=@i+9,fri=@i+10,sat=@i+11where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=4 begin update@day2set mon=@i+5,tue=@i+6,wed=@i+7,thu=@i+8,fri=@i+9,sat=@i+10where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=5 begin update@day2set mon=@i+4,tue=@i+5,wed=@i+6,thu=@i+7,fri=@i+8,sat=@i+9where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=6 begin update@day2set mon=@i+3,tue=@i+4,wed=@i+5,thu=@i+6,fri=@i+7,sat=@i+8where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=7 begin update@day2set mon=@i+2,tue=@i+3,wed=@i+4,thu=@i+5,fri=@i+6,sat=@i+7where sun=@dtchk set@dtchk=@dtchk+6 end insertinto@day2(sun)values(@dtchk+1) set@dtchk=@dtchk+1 if@intchk=1 begin update@day2set mon=@i+15,tue=@i+16,wed=@i+17,thu=@i+18,fri=@i+19,sat=@i+20where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=2 begin update@day2set mon=@i+14,tue=@i+15,wed=@i+16,thu=@i+17,fri=@i+18,sat=@i+19where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=3 begin update@day2set mon=@i+13,tue=@i+14,wed=@i+15,thu=@i+16,fri=@i+17,sat=@i+18where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=4 begin update@day2set mon=@i+12,tue=@i+13,wed=@i+14,thu=@i+15,fri=@i+16,sat=@i+17where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=5 begin update@day2set mon=@i+11,tue=@i+12,wed=@i+13,thu=@i+14,fri=@i+15,sat=@i+16where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=6 begin update@day2set mon=@i+10,tue=@i+11,wed=@i+12,thu=@i+13,fri=@i+14,sat=@i+15where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=7 begin update@day2set mon=@i+9,tue=@i+10,wed=@i+11,thu=@i+12,fri=@i+13,sat=@i+14where sun=@dtchk set@dtchk=@dtchk+6 end insertinto@day2(sun)values(@dtchk+1) set@dtchk=@dtchk+1 if@intchk=1 begin update@day2set mon=@i+22,tue=@i+23,wed=@i+24,thu=@i+25,fri=@i+26,sat=@i+27where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=2 begin update@day2set mon=@i+21,tue=@i+22,wed=@i+23,thu=@i+24,fri=@i+25,sat=@i+26where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=3 begin update@day2set mon=@i+20,tue=@i+21,wed=@i+22,thu=@i+23,fri=@i+24,sat=@i+25where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=4 begin update@day2set mon=@i+19,tue=@i+20,wed=@i+21,thu=@i+22,fri=@i+23,sat=@i+24where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=5 begin update@day2set mon=@i+18,tue=@i+19,wed=@i+20,thu=@i+21,fri=@i+22,sat=@i+23where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=6 begin update@day2set mon=@i+17,tue=@i+18,wed=@i+19,thu=@i+20,fri=@i+21,sat=@i+22where sun=@dtchk set@dtchk=@dtchk+6 end elseif@intchk=7 begin update@day2set mon=@i+16,tue=@i+17,wed=@i+18,thu=@i+19,fri=@i+20,sat=@i+21where sun=@dtchk set@dtchk=@dtchk+6 end /**//*set @i=@i+1 end*/ if@dtchk<>@dnum begin set@dtchk=@dtchk+1 insertinto@day2(sun)values(@dtchk) set@dtval=@dtchk set@dtchk=@dtchk+1 if@dtchk<=@dnum begin update@day2set mon =@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end /**//*insert into @dd(jk1,jk2,jk3)values(@dtchk,@dtval,@dnum)*/ if@dtchk<=@dnum begin update@day2set tue=@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end if@dtchk<=@dnum begin update@day2set wed=@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end if@dtchk<=@dnum begin update@day2set thu=@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end if@dtchk<=@dnum begin update@day2set fri=@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end if@dtchk<=@dnum begin update@day2set sat=@dtchkwhere sun=@dtval set@dtchk=@dtchk+1 end if@dtchk<=@dnum begin insertinto@day2(sun)values(@dtchk) set@dtval=@dtchk set@dtchk=@dtchk+1 if@dtchk<=@dnum begin update@day2set mon=@dtchkwhere sun=@dtval end end end insertinto@DAY1select*from@DAY2 return end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO