create function add_date (@a datetime, @b int) returns datetime as begin declare @b1 int declare @b2 int declare @b3 int select @b1=@b/5 select @b2=@b%5 select @b3=@b2 + datepart(dw,@a) if ( @b3 >5 ) begin select @b2= @b2 +2 end return (dateadd(dd,(@b1*7+@b2),@a)) end go select dbo.add_date(getdate()+1,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 这次和时间设置无关了
向老衲学习!!! select dbo.add_date(getdate(),5)
select dbo.add_date(getdate()+1,25)没问题,呵呵
就是说随便给一天日期,得到五天工作日: declare @dt datetime set @dt=getdate() select @dt 当天日期select top 10 identity(int,1,1) as id into #temp from sysobjects select top 5 @dt+id as 工作日 from #temp where datepart(dw,@dt+id) between 2 and 6 drop table #temp当天日期 ------------------------------------------------------ 2002-11-08 18:50:43.513(所影响的行数为 1 行) 工作日 ------------------------------------------------------ 2002-11-11 18:50:43.513 2002-11-12 18:50:43.513 2002-11-13 18:50:43.513 2002-11-14 18:50:43.513 2002-11-15 18:50:43.513(所影响的行数为 5 行)
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 6drop table #temp
as
begin
declare @b1 int
declare @b2 int
declare @b3 int
select @b1=@b/5
select @b2=@b%5
select @b3=@b2 + datepart(dw,@a)
if ( @b3 >5 )
begin
select @b2= @b2 +2
end
return (dateadd(dd,(@b1*7+@b2),@a))
end
go
select dbo.add_date(getdate()+1,5)
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
这次和时间设置无关了
select dbo.add_date(getdate(),5)
declare @dt datetime
set @dt=getdate()
select @dt 当天日期select top 10 identity(int,1,1) as id into #temp from sysobjects
select top 5 @dt+id as 工作日 from #temp where datepart(dw,@dt+id) between 2 and 6
drop table #temp当天日期
------------------------------------------------------
2002-11-08 18:50:43.513(所影响的行数为 1 行)
工作日
------------------------------------------------------
2002-11-11 18:50:43.513
2002-11-12 18:50:43.513
2002-11-13 18:50:43.513
2002-11-14 18:50:43.513
2002-11-15 18:50:43.513(所影响的行数为 5 行)
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 6drop table #temp