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

I Have to Update City.Date in City table and I have开发者_JAVA百科 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?

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

I know this is old, but I've always been doing Dynamic SQL to make the dateadd function accept a parameter. Well today a different route clicked so I made a function to knock it out for me.

This way I can call it like so

declare @datepart_vc varchar(20)
set @datepart_vc = 'day'
select dbo.Dateadd2(@datepart_vc,1,getdate())

Function

CREATE FUNCTION dbo.DateAdd2
(
    -- Add the parameters for the function here
          @DatePart_VC VARCHAR(20)
        , @Number_IN INT
        , @Date_DT DATETIME
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Return_DT AS DATETIME

    -- Add the T-SQL statements to compute the return value here
    SELECT @Return_DT = (
        CASE 
            WHEN @DatePart_VC = 'year' THEN DATEADD(year,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'quarter' THEN DATEADD(quarter,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'month' THEN DATEADD(month,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'dayofyear' THEN DATEADD(dayofyear,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'day' THEN DATEADD(day,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'week' THEN DATEADD(week,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'weekday' THEN DATEADD(weekday,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'hour' THEN DATEADD(hour,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'minute' THEN DATEADD(minute,@Number_IN,@Date_DT)
            WHEN @DatePart_VC = 'second' THEN DATEADD(second,@Number_IN,@Date_DT)
            --WHEN @DatePart_VC = 'millisecond' THEN DATEADD(millisecond,@Number_IN,@Date_DT)
            --WHEN @DatePart_VC = 'microsecond' THEN DATEADD(microsecond,@Number_IN,@Date_DT)
            --WHEN @DatePart_VC = 'nanosecond' THEN DATEADD(nanosecond,@Number_IN,@Date_DT)

        END

    )

    -- Return the result of the function
    RETURN @Return_DT