Unable to inject smalldatetime into D-SQL statement

Hi, when i try to execute this sql statement i am getting the error.. Conversion failed when converting character string to smalldatetime data type.

Does anyone know what i am doing wrong?

declare @modality varchar(50)
declare @datefrom smalldatetime

set @modality = 'xxxxxxx'
set @datefrom = '20090101'

declare @var1 nvarchar(4000)
select @var1 = 
    'select
     sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +'
    from dbo.vw_RawData
    where vw.date >= ' + @datefrom + ''

exec sp_executesql @var1

From stackoverflow $pageUsers[$entry.posts[0].author].name

• You are trying to concatenate the smalldatetime with a varchar. Change

  Solution 1

  declare @datefrom smalldatetime
  

  to

  declare @datefrom varchar(8)
  

  and

  select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +
  ' from dbo.vw_RawData where vw.date >= ' + @datefrom + ''
  

  to

  select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +
  ' from dbo.vw_RawData where vw.date >= ''' + @datefrom + ''''
  

  Solution 2

  change

  select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +
  ' from dbo.vw_RawData where vw.date >= ' + @datefrom + ''
  

  to

  select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +
  ' from dbo.vw_RawData where vw.date >= ''' + convert(varchar(10), @datefrom, 121) + ''''
  

  From a programmer

• In the statement select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +' from dbo.vw_RawData where vw.date >= ' + @datefrom + '' SQL Server is trying to do date arithmetic by casting all the surrounding strings to a smalldatetime instead of converting @datefrom to a string and performing string concatenation.

  Possible fixes.

  • Change @DateFrom to a sting so that the concatenation works. Note you will have to add some quotes so that the string in @Var1 is properly formated.
  • Use convert function to convert @datefrom to a string. Look up the right conversion number in Books online. I don't have time to right now. Don't use cast, it won't give a
  • Use a paramertized SQL String. Look up sp_executesql in Books Online. (Or wait, StackOverflow always has someone to point out how to avoid dynamic SQL.)

  EDIT: Just checked. cast(@DateTime as Varchar(...)) gives a string that I thought might be hard to parse, but it seems to work, might try that instead of convert. Make sure the varchar() is big enough

  From Shannon Severance