thank you.
i have tried this bid and its workking.But at the point it suppose to be 200701 it selects it self (weekno) = 200653...but for 200654 it become 200702 which is correct ..i have tried to modify...not much helpful to solve it.
how about now:
declare @yourdata as int
declare @addthisweek as int
select @yourdata = 200601, @addthisweek = 52
select @yourdata, @addthisweek
select cast(datepart(year,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4))+
right('00' + cast(datepart(week,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4)),2),
(dateadd(week,@addthisweek,dateadd(week,cast(subst ring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime))))
another thing, if you check datepart(week,'12/31/2006') it will return you 53. the reason is, it's the 53rd week of the year. so you might want to do an if @addthisweek = 53 @addthisweek = @addthisweek - 1 kind of condition...