22,210
社区成员
发帖
与我相关
我的任务
分享
create table tb(date datetime,id int)
insert tb
select '2011-01-01',1 union all
select '2011-01-02',1 union all
select '2011-01-03',1 union all
select '2011-02-02',1 union all
select '2011-02-03',1 union all
select '2011-02-04',1 union all
select '2011-02-05',1 union all
select '2011-02-06',1 union all
select '2011-02-07',1 union all
select '2011-02-08',1
--上面这个表用来添加所有放假日期 其他的自己再加
create table tb(date datetime,id int)
insert tb
select '2011-01-01',1 union all
select '2011-01-02',1 union all
select '2011-01-03',1 union all
select '2011-02-02',1 union all
select '2011-02-03',1 union all
select '2011-02-04',1 union all
select '2011-02-05',1 union all
select '2011-02-06',1 union all
select '2011-02-07',1 union all
select '2011-02-08',1
--其他的自己再加
drop function dbo.f_day
create function dbo.f_day(@date datetime,@days int) returns datetime
as
begin
declare @date1 datetime
select @date1=dateadd(dd,number,@date) from master..spt_values
where type='p' and dateadd(dd,number,@date) not in (select date from tb)
and @days=number+1-(select count(*) from tb where date between @date and dateadd(dd,number,@date))
return @date1
end
select dbo.f_day('2011-02-01',2)
/*
select dateadd(dd,number,'2011-02-01') from master..spt_values
where type='p' and dateadd(dd,number,'2011-02-01') not in (select date from tb)
and 2
*/