February 27, 2009 at 12:51 pm
Hi,
why i can't use the first variable in the dateadd(the second variable works good)?
declare @TypeOfClean nvarchar (10),@CleanNumber smallint
set @TypeOfClean = 'dd'
set @CleanNumber = 2
select dateadd (@TypeOfClean,-@CleanNumber,getdate())
--the Error = Invalid parameter 1 specified for dateadd.
THX
February 27, 2009 at 1:01 pm
You can't use a variable to pass in that DATEADD parameter. The only way I know of to do what you're attempting is to use dynamic SQL like this:
DECLARE @TypeOfClean NVARCHAR (10),@CleanNumber SMALLINT, @SQL NVARCHAR(MAX)
SET @TypeOfClean = 'dd'
SET @CleanNumber = 2
SET @SQL = 'SELECT DATEADD (' + @TypeOfClean + ',-' + CONVERT(NVARCHAR(5), @CleanNumber) + ',GETDATE())'
EXECUTE sp_executesql @SQL
Greg
February 27, 2009 at 1:15 pm
Easier with a case statement. There are only so many variations on the interval.
case @Type
when 'dd' then dateadd(day, @Number, MyDate)
when 'ww' then dateadd(week, @Number, MyDate)
when 'mm' then dateadd(month, @Number, MyDate)
when 'yy' then dateadd(year, @Number, MyDate)
end
No dynamic SQL needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply