Insert by date using Openquery using SQL Server Linkedserver?-CodePudding

I use the below Openquery statement to connect to the linked server and then I want to fetch those records to my local server. The problem is that I want to change the date every day in the where statement below, so I want to make an Openquery by putting the system date as a variable, but an error occurs. Please help me.

INSERT INTO [A-SERVER\SQLEXPRESS].[ASQL].[dbo].[ATB] 
SELECT * FROM OPENQUERY ([LINKB], 'SELECT T.DATE, T.ID, O.DES,S.PR,SUM(T.TQ), P.T1, P.T2,  
P.T3, P.T4, SUM(T.TL) 
FROM [BSQL].[dbo].[BDL] T
JOIN [BSQL].[dbo].[OBT] O ON O.ID = T.ID
JOIN [BSQL].[dbo].[POT] P ON P.ID = T.ID
JOIN [BSQL].[dbo].[ICE] S ON S.ID = T.ID
WHERE CAST(T.DATE AS DATE) = ''2022-07-28'' AND T.TOL = ''3'' 
GROUP BY T.DATE, T.ID, O.DES, S.PR, P.T1, P.T2,P.T3, P.T4 ');
GO

CodePudding user response：

use GETDATE() cast it as Date

Returns the current a DateTime value of the database system

select CAST(GETDATE() AS DATE); --2022-07-29

So in your condition change the static date ''2022-07-28'' with GETDATE() to get the date dynamically:

WHERE CAST(T.DATE AS DATE) = CAST(GETDATE() AS DATE)