I've used SQL "merge" commands to update SQL data from a read-only data source.

Try your dates in this format: {d 'yyyy-mm-dd'}

I have tried to format this a few ways. This is in SSMS as I am going to write as SP that is scheduled out.

Without the WHERE clause, the data is returned in full. However, I need to filter to not pull 2.3 million rows of history every time.

Here is one example:

  	SELECT	*, GETDATE() AS 'ExtractDate'
	FROM OPENQUERY (SAGE100LS, '
	SELECT 
			*
	FROM	
			SO_SalesOrderHeader
	WHERE
			OrderDate = FORMAT(GetDate(), ''yyyy-MM-dd'')
	')

Here is the error that is returned:

OLE DB provider "MSDASQL" for linked server "SAGE100LS" returned message "[ProvideX][ODBC Driver]Unexpected extra token: (".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
    SELECT
            *
    FROM    
            SO_SalesOrderHeader
    WHERE
            OrderDate = FORMAT(GetDate(), 'yyyy-MM-dd')
    " for execution against OLE DB provider "MSDASQL" for linked server "SAGE100LS".

{d 'yyyy-mm-dd'} not just 'yyyy-mm-dd'

Kevin,

That returned results. Thank you!!

My goal is perform a master data dump and do a rolling delta every few hours for updates
If I am trying to do a GETDATE()-1, would I just declare as DATE and then call the variable in my SP?

It has been a while since I set up something with a dynamic date as part of the query, but remember having to play around with a bit of T-SQL syntax to get it working... in my case I think I was using a batch parameter though instead of hard-coded logic, which might have made things harder.

It has been a while since I set up something with a dynamic date as part of the query, but remember having to play around with a bit of T-SQL syntax to get it working... in my case I think I was using a batch parameter though instead of hard-coded logic, which might have made things harder.

With the method I proposed above, can you think of another way I can pull the delta dynamically?
Thank you again for the quick responses!!

You know how the query needs to be structured... just play with the syntax until you find something that works.

Ok, let me give it a shot. Once I find something that works, I will let you know.

Thank you again!!