Moving T-SQL datatime column for X days overlapping UNIQUE constraint

Question

The problem is to use DATEADD function on column with unique value constraint taking into consideration the fact that new values will overlap existing values and in fact there will be violation of constraint, because we can not have two rows with the same date.

e.g. I have table with column [SomeDate] which is of type DateTime and has constraint to be unique. I have dates starting from 2017-01-01 to 2018-01-01 and want to update records by adding 7 days to each of them.

Answer 1

If you update all rows, there should be no problem with a unique constraint.
Here is a quick example:

CREATE TABLE T
(
    SomeDate date NOT NULL,
    CONSTRAINT uc UNIQUE (SomeDate)
)

;WITH CTE AS
(
    SELECT CAST(GETDATE() As Date) As TheDate
    UNION ALL 
    SELECT CAST(DateADD(DAY, 1, TheDate) As Date)
    FROM CTE
    WHERE TheDate < DATEADD(DAY, 10, GETDATE())
)

INSERT INTO T(SomeDate)
SELECT TheDate
FROM CTE

UPDATE T
SET SomeDate = DATEADD(DAY, 3, SomeDate)

You can see it in action on rextester

Answer 2

One of the possible way is to move the dates ahead to go out of current min-max range and then bring them back taking into account how many days we want to add. Here is ready and working solution:

--Number of days we want to add to existing dates
DECLARE @daysToMoveAhead int = 7;

DECLARE @minDate datetime = (SELECT MIN([SomeDate]) from dbo.MyTable)
DECLARE @maxDate datetime = (SELECT MAX([SomeDate]) from dbo.MyTable)

DECLARE @diff int = DATEDIFF(DAY,@minDate,@maxDate)

--temporary move the dates out of existing min-max range
update dbo.MyTable set [SomeDate] = DATEADD(DAY, @diff,[SomeDate]);

--bring dates back and add as many days as we wanted
update dbo.MyTable set [SomeDate] = DATEADD(DAY, @daysToMoveAhead - @diff,[SomeDate]);