Lynn Pettis (12/2/2012)
kapil_kk (12/2/2012)
the script you have written is not satisfy the scenario that i wrote..You are going to have to tell me more than that it doesn't work. If I give it 2012-01-01 and 30 days, it returns 2012-02-04, just like you posted.
Maybe it doesn't satisify your actual requirements, but you really aren't giving us much to work with. We need the DDL (CREATE TABLE) statement for your table, some sample data as a seried of INSERT INTO statements, and the expected results based on the sample data. With that information you will get a better answer.
This appears to work if you stay in the same year:
declare @TestDate date = '20120201',
@DaysToAdd int = 30;
select
@TestDate,
@DaysToAdd,
dateadd(dd, @DaysToAdd, @TestDate),
datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)),
datepart(wk,@TestDate),
dateadd(dd, datepart(wk,dateadd(dd, @DaysToAdd, @TestDate)) - datepart(wk,@TestDate), dateadd(dd, @DaysToAdd, @TestDate));