sql subtract dates

Some of the dates in a column of dates were recorded wrong. I'd l开发者_C百科ike to make a query which subtracts one day from each date IF the days are in a certain date range.

I know I'll have to use DATEADD and UPDATE, but I can't seem to figure it out. Thanks in advance.

This should do it:

UPDATE  [SomeTable]
SET     [DateColumn] = DATEADD(d, -1, [DateColumn])
WHERE   [DateColumn] BETWEEN [Date1] AND [Date2]

Here's the MSDN doc's on the DATEADD function: http://msdn.microsoft.com/en-us/library/ms186819.aspx

When performing updates on data like this, it's always best to run a select statement first with the same criteria to ensure that you're updating the correct records. It also helps reduce the stress level of updating (especially if you're unfamiliar with SQL).

SELECT    *, --Depending on what columns you would like to see, the wildcard could be replaced
          DATEADD(d, -1, [DateColumn]) AS ProposedDate
FROM      [SomeTable]
WHERE     [DateColumn] BETWEEN [Date1] AND [Date2]

DECLARE @min_date datetime, @max_date datetime;

UPDATE yourtable
SET date_column = DATEADD(day, -1, date_column)
WHERE id IN (
  SELECT id
  FROM yourtable
  WHERE date_column BETWEEN @min_date AND @max_date
);

You'll have to set appropriate values for @min_date and @max_date.

UPDATE MyTable
SET DateField = DATEADD(Day, -1, DateField)
WHERE Datefield BETWEEN '1/1/2011' AND '2/1/2011'

use UPDATE
set the data = ( do the date math )
WHERE the date is in the range you want
and the key is the row you want.