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?

Brad · Accepted Answer · 2014-05-28 21:34:20Z

17

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

edited May 28, 2014 at 21:34

Brad

12.4k4 gold badges52 silver badges82 bronze badges

answered Aug 2, 2011 at 7:49

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gbn Over a year ago

@Avinash: you're welcome. Please vote up and/or accept my answer then (the up and the tick on the left). See meta.stackexchange.com/questions/7237/how-does-reputation-work

Jeff Beagley · Accepted Answer · 2016-12-30 19:19:01Z

0

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

answered Dec 30, 2016 at 19:19

Jeff Beagley

1,02511 silver badges22 bronze badges

3 Comments

MartinThurn Over a year ago

Is your function declaration missing the END keyword?

Jeff Beagley Over a year ago

@MartinThurn I do not see an end required in official documentation. learn.microsoft.com/en-us/sql/t-sql/statements/…

Jeff Beagley Over a year ago

@MartinThurn but this was 7 years ago.. not sure I can recall the intent of this function. It seems redundant with DATEADD and unnecessary. Wish I could remember the intent.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related