操作数类型冲突：日期尝试使用动态SQL时与INT不兼容

Question

我创建了一个SQL语句，该语句使用变量用于开始日期和结束日期，这是

  declare @startDate varchar(20) = '2022-06-01'
  declare @endDate varchar(20) = '2022-06-29'

  select distinct instanceId as [JourneyID], 
  CAST((select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) as varchar) as [AuditDate]
  ,CAST((select case when ISDATE(UpdateDate) = 1 THEN CAST(UpdateDate as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 176537 and instanceId = qanda.instanceId) as varchar) as [UpdatedDate]
  from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] qanda
  where (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) >= CAST(@startDate as date)
  and (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) <= CAST(@endDate as date)
  and UpdateDate is not null

我尝试使用Dynamic SQL运行的，因为我需要动态传递服务器和数据库名称，因为此查询从SSRS报告中使用。

我得到了操作数类型冲突：日期与int不兼容

这是我的动态sql，

@startDate varchar(15),
@endDate varchar(15),

set @OPENQUERY = 'Select [JourneyID], [AuditDate], [UpdatedDate] from OPENQUERY(' + @ServerName + ','''
set @sql = 'Select distinct instanceId as [JourneyID], 
  CAST((select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) as varchar) as [AuditDate]
  ,CAST((select case when ISDATE(UpdateDate) = 1 THEN CAST(UpdateDate as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 176537 and instanceId = qanda.instanceId) as varchar) as [UpdatedDate]
from ' + @DatabaseName + '.dbo.AB_SurveyQandA qanda where 
(select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) >= ' + CAST(@startDate as nvarchar) + '
  and (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) <= ' + CAST(@endDate as nvarchar) +
' and UpdateDate is not null 
'')'

我尝试铸造@startdate和 @enddate作为日期，但后来说日期与操作数不相容 +

我需要做些什么才能获得此功能。在动态SQL字符串中工作吗？

Answer 1

根据Ed Bangga的建议，这里是SQL打印的结果
这是印刷SQL的结果

Select [JourneyID], [AuditDate], [UpdatedDate] from OPENQUERY(MYSTERYDB,'Select distinct instanceId as [JourneyID], 
  CAST((select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) as varchar) as [AuditDate]
  ,CAST((select case when ISDATE(UpdateDate) = 1 THEN CAST(UpdateDate as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 176537 and instanceId = qanda.instanceId) as varchar) as [UpdatedDate]
from AbellioWMTData_April_21.dbo.AB_SurveyQandA qanda where 
(select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) >= '2022-06-01'
  and (select case when ISDATE(questionComment) = 1 THEN CAST(questionComment as date) END from [AbellioWMTData_April_21].[dbo].[AB_SurveyQandA] where protoQuestionId = 170346 and instanceId = qanda.instanceId ) <= '2022-06-29'
 and UpdateDate is not null 
')