send datepart as parameter from a table to DATEADD function in sql server

Question

I Have to Update City.Date in City table and I have columns in the table like Interval and Period in the City table.

Interval column contains values like yy,ww,dd,qq,etc and Period column contains values like 1,2,3.

I am trying to update City.Date like this:

UPDATE City 
SET City.date = DATEADD(City.Interval, City.Period, City.date)
WHERE CityId = 13

It is getting error like:

City.Interval is not recognized DATEADD option.

How can I update City.Date using City.Interval, City.Period and City.date?

Answer 1

You can't parameterise the interval bit

UPDATE City 
SET date = CASE Interval
              WHEN 'yy' THEN DATEADD(yy, Period, date)
              WHEN 'ww' THEN DATEADD(ww, Period, date)
              WHEN 'dd' THEN DATEADD(dd, Period, date)
              WHEN 'qq' THEN DATEADD(qq, Period, date)
              WHEN ...
           END
WHERE CityId =13