use collumn value in dateadd function

[fiep]

Hi,

I have a collumn that contains the first parameter for the dateadd function. (i.e. WEEK, MONTH, etc)
I want to use this value in the where statement of a query but get the following error:

' + cast(T.AddString as varchar ) + ' is not a recognized dateadd option.

Or

Invalid parameter 1 specified for dateadd.

These are some of the code options I tried:
dateadd(cast(T.AddString as varchar) , cast(t.AddNumber as int) , GETDATE())

dateadd(' + cast(T.AddString as varchar) +' , cast(t.AddNumber as int) , GETDATE())

dateadd("'" + cast(T.AddString as varchar) +"'" , cast(t.AddNumber as int) , GETDATE())



Is it possible to use a collumn value in a dateadd function?

Thanks

 

[snitin78]

You can do this directly. You will have to use dynamic SQL for this and get the result out to your main SQL (if any). Something like:

declare @sql nvarchar(800),
@output datetime

set @sql = 'select @output = dateadd(' +cast('Day' as varchar)+ ', cast(' +cast(1 as varchar)+ ' as int), getdate())'

EXEC sp_executesql
@sql,
N'@output datetime OUTPUT',
@output OUTPUT
select @output

 

[snitin78]

In the above query, replace cast('Day' as varchar) with T.AddString and cast(1 as varchar) with cast(T.AddNumber as varchar)