sp_executesql特定情况下的日期"Conversion failed when converting date and/or time from character string"

Question

我有点奇怪:

Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = GETDATE()
Set @dateColumn = 'ERDAT'
Set @SQLQuery = 'SELECT * FROM myTable WHERE @dateColumn <= @maxdat'
Execute sp_executesql @SQLQuery, N'@maxdat date, @dateColumn nvarchar(10)', @maxdat, @dateColumn

对于Conversion failed when converting date and/or time from character string.

，这将失败

但是下面的代码可以正常工作:

Set @SQLQuery = 'SELECT * FROM myTable WHERE ERDAT <= @maxdat'

Answer 1

像这样尝试更新后的代码。只需将@dateColumn参数放在查询字符串

之外

Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = GETDATE()
Set @dateColumn = 'ERDAT'
Set @SQLQuery = 'SELECT * FROM myTable WHERE ' + @dateColumn + ' <= @maxdat'
Execute sp_executesql @SQLQuery, N'@maxdat date', @maxdat

Answer 2

问题是你的动态SQL。你这样做的方式，变量将作为字符串。你必须使用"+"符号，并像下面的例子一样，这样SQL就可以解析它，并使用"变量值"而不是"变量名"。

SELECT GETDATE() AS A
INTO 
#Temp
Declare @SQLQuery As nvarchar(Max)
Declare @maxdat date
Declare @dateColumn nvarchar(10)
Set @maxdat = DATEADD(Day,1,GETDATE())
Set @dateColumn = 'A'
--Set @SQLQuery = 'SELECT * FROM #Temp WHERE @dateColumn >= @maxdat'
Set @SQLQuery = 'SELECT * FROM #Temp WHERE ' + @dateColumn + ' >= '+ CAST(@maxdat AS nvarchar(10))
--Print @SQLQuery
Execute sp_executesql @SQLQuery, N'@maxdat date, @dateColumn nvarchar(10)', @maxdat, @dateColumn