按工作日加减天数,忽略星期六日
/* * * * * * * * * * * * * * * * * * * * *
* 功能:按工作日加减天数,忽略星期六日。*
* 适用:sql的有效日期范围之内。 *
* 作者:Limpire(昨夜小楼) *
* * * * * * * * * * * * * * * * * * * * */
create function fn_workdayadd(@date datetime, @days int)
returns datetime
as
begin
declare @weekday smallint, @sign smallint
select @weekday = (@@datefirst+datepart(weekday,@date)-1)%7, @sign = sign(@days)
if @days < 0 set @weekday = @weekday-6
set @date = dateadd(day,(@days+(@days+@weekday-@sign-@weekday/6)/5*2-@weekday/6)*abs(@sign),@date)
return (@date)
end
go
select dbo.fn_workdayadd(getdate(),-3) --> 2008-05-09 10:54:53.857
* 功能:按工作日加减天数,忽略星期六日。*
* 适用:sql的有效日期范围之内。 *
* 作者:Limpire(昨夜小楼) *
* * * * * * * * * * * * * * * * * * * * */
create function fn_workdayadd(@date datetime, @days int)
returns datetime
as
begin
declare @weekday smallint, @sign smallint
select @weekday = (@@datefirst+datepart(weekday,@date)-1)%7, @sign = sign(@days)
if @days < 0 set @weekday = @weekday-6
set @date = dateadd(day,(@days+(@days+@weekday-@sign-@weekday/6)/5*2-@weekday/6)*abs(@sign),@date)
return (@date)
end
go
select dbo.fn_workdayadd(getdate(),-3) --> 2008-05-09 10:54:53.857