CRM and GetDate()

[JBird]

Is it possible to use getdate() or similar to get recently updated records (rather than pulling full dataset).

Example being:

Code: Select all

SELECT * FROM OPENQUERY(CRM,'SELECT * FROM quotedetail WHERE createdon >= getdate()-14') CRM

(OpenQuery used for performance, rather than pulling the full table to the local SQL server.)

This returns this error message which implies that the function getdate() is not implemented:

Code: Select all

SQL.sql: OLE DB provider "MSDASQL" for linked server "CRM" returned message "[Devart][ODBC][Dynamics 365]SQLite error
no such function: getdate".
SQL.sql: Error (9,1): Cannot get the column information from OLE DB provider "MSDASQL" for linked server "CRM".

In theory could build up the SQL and then use EXECUTE to get the data, however this is very messy - getdate() is simpler.

[MaximG]

Devart ODBC Driver for Dynamics 365 uses SQLite engine to compose queries, therefore, you can use the syntax used in SQLite : http://www.sqlite.org/lang.html . To implement the described behavior, you can use the DATE function, for example :

Code: Select all

SELECT * FROM OPENQUERY(CRM, 'SELECT * FROM quotedetail WHERE createdon >= Date(''now'', ''-14 day'')') 

[JBird]

Many thanks!