无法将smalldatetime注入D-SQL语句
当我尝试执行此sql语句时，我收到错误。将字符串转换为smalldatetime数据类型时转换失败。
有谁知道我做错了什么？
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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在尝试将smalldatetime 与varchar 连接起来。
更改
将解决方案 1
为
，
将
解决方案 2
更改
为
You are trying to concatenate the smalldatetime with a varchar.
Change
Solution 1
to
and
to
Solution 2
change
to
在语句中
select @var1 = 'select sum('+ @modality +') as ' + dbo.fnc_titlecase(@modality) +' from dbo.vw_RawData where vw.date >= ' + @datefrom + ' 'SQL Server 尝试通过将所有周围的字符串转换为smalldatetime 来进行日期算术，而不是将@datefrom 转换为字符串并执行字符串连接。可能的修复。
连接有效。 注意你
必须添加一些引号，以便
@Var1 中的字符串正确
格式化。
编辑：刚刚检查过。 cast(@DateTime as Varchar(...)) 给出了一个我认为可能难以解析的字符串，但它似乎有效，可以尝试而不是转换。 确保 varchar() 足够大
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.
the concatenation works. Note you
will have to add some quotes so that
the string in @Var1 is properly
formated.
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