DATEADD(day, 5, table1.someday) 这样只能得到表日期字段的5天以后
我希望得到的是5个工作日就是说如果是周末就跳过不计
---------------------------------------------------------------
create function add_date (@a datetime, @b int) returns datetime
as
begin
declare @b1 int
declare @b2 int
declare @b3 int
declare @dayofweek int
select @dayofweek = datepart(dw,@a) + @@datefirst - 1
if @dayofweek > 7 select @dayofweek = @dayofweek - 7
if @dayofweek > 5
begin
select @a =dateadd (dd,8-@dayofweek,@a)
end
select @dayofweek = datepart(dw,@a) + @@datefirst - 1
if @dayofweek > 7 select @dayofweek = @dayofweek - 7
select @b1=@b/5
select @b2=@b%5
select @b3=@b2 + @dayofweek
if ( @b3 >5 )
begin
select @b2= @b2 +2
end
return (dateadd(dd,(@b1*7+@b2),@a))
end
这次和时间设置无关了
---------------------------------------------------------------
declare @dt datetime
set @dt=getdate()
select top 7 identity(int,1,1) as id into #temp from sysobjects
--得到给定日期起后5天：
select @dt+id as 工作日 from #temp where datepart(dw,@dt+id) between 2 and 6
--得到给定日期所在星期的5天工作日：
select @dt-datepart(dw,@dt)+id as 工作日 from #temp where datepart(dw,@dt-datepart(dw,@dt)+id) between 2 and 6
drop table #temp