I am trying to create a query for an SSRS report. I need to get the last 3 months transactions from a linked server (to an iSeries).
So I have built a SQL variable that I want to execute with OpenQuery
declare @EarliestDate varchar(8), @SQL VARCHAR(200), @sDate varchar(8)
SET @EarliestDate=CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4))
+ RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-3, getdate())) AS varchar(2)), 2)+ '01'
SELECT @SQL= 'SELECT * FROM YEDB03P WHERE TCDAT >' + '''' + @EarliestDate +'''' + ' ORDER BY TCDAT DESC'
print @EarliestDate
print @SQL
select * from openquery(BOCTEST2,@SQL)
But it seems that openquery
doesn't like using a variable
What's the correct way to do this?
with help from Oleksandr Kucher I managed to do what I want with the below code (I added char(39)
to the query string to force the quotes):
declare @EarliestDate varchar(8), @SQL NVARCHAR(1000), @sDate varchar(8)
SET @EarliestDate= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4))
+ RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '01'
SELECT @SQL= 'insert into DBO.YEDB03P select * from openquery(BOCTEST2, ''SELECT * FROM YEDB03P WHERE TCDAT >'+char(39)+CHAR(39) + @EarliestDate +char(39)+CHAR(39) + ' ORDER BY TCDAT DESC'')'
--print @SQL
/*
The prints as
insert into DBO.YEDB03P select * from openquery(BOCTEST2, 'SELECT * FROM YEDB03P WHERE TCDAT >''20141001'' ORDER BY TCDAT DESC')
*/
exec sp_executesql @SQL
select * from PION..YEDB03P
thanks