Incrementing a counter for dateadd

Question

Hello,

I'm trying to increase the time between dates by a set amount. For example, I want to add two months to the time between date 1,2 and 3. I'm having trouble incrementing my 'date counter'...

DECLARE @monthDiff int

SET @monthDiff = 1;

UPDATE [Table1]
SET [Date] = DATEADD(MONTH, (SET @monthDiff = @monthDiff + 1), [Date])
WHERE [ID] IN 
(
    SELECT [ID]
    FROM [Table2]
    WHERE [Description] = 'HE'
);

An example might help...

Original dates:

01/04/1984
01/05/1984
01/06/1984

New dates:

01/04/1984
01/06/1984
01/08/1984

Any ideas?

I'm using SQLServer 2005.

Thanks.

Answer 1

this should work, although only if each original date in your table is unique. I've called the column mydate because I don't think date is a legal (or sensible) name for a column. This could be easier and safer if you've got some kind of ID column. then we would use that for the cursor rather than the date

declare @numberOfMonthsToAdd int
declare @currentDate dateTime
set @numberOfMonthsToAdd = 0
declare myCursor cursor  for 
select [mydate] from Table1
open myCursor
while @@fetch_Status<>-1
begin
fetch next from myCursor into @currentDate
update Table1 set [mydate] = DATEADD(month,@numberOfMonthsToAdd, [mydate]) where [mydate] = @currentDate
set @numberOfMonthsToAdd = @numberOfMonthsToAdd + 1

end
close mycursor
deallocate mycursor

Answer 2

From your example, it seems you want to change the dates relative to a base date:

declare @basedate datetime
select @basedate = min([Date]) FROM Table1 WHERE ...

UPDATE [Table1]
SET [Date] = DATEADD(MONTH, DATEDIFF(MONTH, @basedate, [Date]) * 2, [Date])
WHERE ...

Answer 3

;WITH cte AS
(
SELECT t1.ID, 
       t1.[Date], 
       ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY [Date]) AS RN
FROM Table1 t1
JOIN Table2 t2
ON t1.ID = t2.ID 
WHERE t2.[Description] = 'HE'
)
UPDATE CTE
SET [Date] = DATEADD(MONTH, RN-1, [Date])