Unable to inject smalldatetime into D-SQL statement

Question

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

Answer 1

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) + ''''

Answer 2

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