MSSQL生成日期列表代码

ifexists(select*fromdbo.sysobjectswhereid=object_id(N"[dbo].[f_getdate]")andxtypein(N"FN",N"IF",N"TF"))
dropfunction[dbo].[f_getdate]
GO
createfunction[dbo].[f_getdate]
(
@yearint,--要查询的年份
@bzbit--@bz=0查询工作日,@bz=1查询休息日,@bzISNULL查询全部日期
)
RETURNS@reTABLE(Datedatetime,Weekdaynvarchar(3))
as
begin
DECLARE@tbTABLE(IDint,Datedatetime)
insert@tbselectnumber,
dateadd(day,number,DATEADD(Year,@YEAR-1900,"1900-1-1"))
frommaster..spt_valueswheretype="P"andnumberbetween0and366
DELETEFROM@tbWHEREDate>DATEADD(Year,@YEAR-1900,"1900-12-31")
IF@bz=0
INSERTINTO@re(Date,Weekday)
SELECTDate,DATENAME(Weekday,Date)
FROM@tb
WHERE(DATEPART(Weekday,Date)+@@DATEFIRST-1)%7BETWEEN1AND5
ELSEIF@bz=1
INSERTINTO@re(Date,Weekday)
SELECTDate,DATENAME(Weekday,Date)
FROM@tb
WHERE(DATEPART(Weekday,Date)+@@DATEFIRST-1)%7IN(0,6)
ELSE
INSERTINTO@re(Date,Weekday)
SELECTDate,DATENAME(Weekday,Date)
FROM@tb

RETURN
end
go
select*fromdbo.[f_getdate]("2009",0)