sql server语言 - 从字符串转换日期和/或时间失败

Question

我正在编写一个动态查询，我需要将datetime变量转换为时间变量

DECLARE @pdatetime_GarageOpenFrom DATETIME = '2013-07-25 08:00:00'
declare  @str_SQLCount2 varchar(max)
set @str_SQLCount2 = 'select (CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)'
print(@str_SQLCount2)
exec(@str_SQLCount2)

Answer 1

当然这会产生一个错误。变量@pdatetime_GarageOpenFrom在exec语句的上下文中是未知的。

你基本上有两个选择。要么在sql字符串中声明变量，要么使用sp_executesql()。

declare  @str_SQLCount2 varchar(max)
set @str_SQLCount2 = '
DECLARE @pdatetime_GarageOpenFrom DATETIME = ''2013-07-25 08:00:00''
select CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)'
print(@str_SQLCount2)
exec(@str_SQLCount2)

首选方法是sp_execute_sql与您的原始字符串:

DECLARE @pdatetime_GarageOpenFrom DATETIME = '2013-07-25 08:00:00'
declare  @str_SQLCount2 varchar(max);
set @str_SQLCount2 = 'select CONVERT(CHAR(10), @pdatetime_GarageOpenFrom, 111)';
print(@str_SQLCount2);
exec sp_executesql @str_SQLCount2,
                   N'@pdatetime_GarageOpenFrom DATETIME',
                   @pdatetime_GarageOpenFrom = @pdatetime_GarageOpenFrom;