27,579
社区成员
发帖
与我相关
我的任务
分享
-- 更新前
select * from TA
/*
TAID StartDate EndDate DateDays DateType
----------- ----------------------- ----------------------- ----------- -----------
1 2016-07-01 00:00:00.000 2016-07-07 00:00:00.000 5 1
2 2016-07-08 00:00:00.000 2016-07-08 00:00:00.000 1 2
(2 row(s) affected)
*/
-- 建函数
create function dbo.fn_getNewdate
(@fdate datetime,
@delay int,
@type int) returns datetime
as
begin
declare @y datetime,@i int
select @y=@fdate,@i=1
if (@type=1)
begin
while(@i<=@delay)
begin
select @y=dateadd(d,1,@y)
if exists(select 1 from Calendar where CDate=@y and IsWork=1)
begin
select @i=@i+1
end
end
end
else
begin
select @y=dateadd(d,@delay,@y)
end
return @y
end
-- 更新
declare @taid int,@delay int
select @taid=1, -- 任务ID
@delay=2 -- 延期天数
update TA
set StartDate=dbo.fn_getNewdate(StartDate,@delay,DateType),
EndDate=dbo.fn_getNewdate(EndDate,@delay,DateType)
where TAID=@taid
or TAID in(select TAID from TB where PrefixTAID=@taid)
-- 更新后
select * from TA
/*
TAID StartDate EndDate DateDays DateType
----------- ----------------------- ----------------------- ----------- -----------
1 2016-07-05 00:00:00.000 2016-07-11 00:00:00.000 5 1
2 2016-07-10 00:00:00.000 2016-07-10 00:00:00.000 1 2
(2 row(s) affected)
*/