将datepart作为参数从表中发送到sql server中的DATEADD函数

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

我必须在City表中更新City.Date，并且我在表格中有Interval和Period等列。

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

Interval列包含yy，ww，dd，qq等值，Period列包含1,2,3等值。

I am trying to update City.Date like this:

我想尝试像这样更新City.Date：

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.

City.Interval无法识别DATEADD选项。

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

如何使用City.Interval，City.Period和City.date更新City.Date？

2 个解决方案

#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

#2

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.

我知道这是旧的，但我一直在做动态SQL，使dateadd函数接受一个参数。那么今天点击了一条不同的路线，所以我做了一个功能，为我敲出它。

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

#1