复制代码 代码如下:
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_getdate]’) and xtype in (n’fn’, n’if’, n’tf’))
drop function [dbo].[f_getdate]
go
create function [dbo].[f_getdate]
(
@year int, –要查询的年份
@bz bit –@bz=0 查询工作日,@bz=1 查询休息日,@bz is null 查询全部日期
)
returns @re table(date datetime,weekday nvarchar(3))
as
begin
declare @tb table(id int ,date datetime)
insert @tb select number,
dateadd(day,number,dateadd(year,@year-1900,’1900-1-1′))
from master..spt_values where type=’p’ and number between 0 and 366
delete from @tb where date>dateadd(year,@year-1900,’1900-12-31′)
if @bz=0
insert into @re(date,weekday)
select date,datename(weekday,date)
from @tb
where (datepart(weekday,date)+@@datefirst-1)%7 between 1 and 5
else if @bz=1
insert into @re(date,weekday)
select date,datename(weekday,date)
from @tb
where (datepart(weekday,date)+@@datefirst-1)%7 in (0,6)
else
insert into @re(date,weekday)
select date,datename(weekday,date)
from @tb
return
end
go
select * from dbo.[f_getdate](‘2009’,0)