1

I am trying to run a select using openquery with filtering result by date, but I have problem with using the date after where clause.

Ideally I would like to be able to pass a variable 

set @d = dateadd(day, -30, getdate())

but for the sake of example I will try to use specified date :

Example: 

select * 
from OPENQUERY([Linked_Server], 'select id, name from Users where LastModifiedDate > ''2017-01-01''')

This returns an error:

INVALID_FIELD:
select id, name from Users where LastModifiedDate > '2017-01-01'
value of filter criterion for field 'LastModifiedDate' must be of type dateTime and should not be enclosed in quotes".

It works ok if I use for example istrue = true, but comparing dates seems to be the problem.

Can someone please advise me on this ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adrian
  • 347
  • 1
  • 6
  • 18

2 Answers2

2

It looks like you're querying a linked server that is not standard SQL Server but is instead Salesforce which uses SOQL which has a specific format for date and datetime literals. The correct format for date filters in Salesforce is:

WHERE LastModifiedDate > 2017-01-01T00:00:00Z

So your full SQL should be:

SELECT *
FROM OPENQUERY(
    [Linked_Server],
    'SELECT id, name FROM Users WHERE LastModifiedDate > 2017-01-01T00:00:00Z')
DavidG
  • 113,891
  • 12
  • 217
  • 223
2

WE use a lot of Open queries here and we've stumbled into that kind of scenario. What we have done in the past is this:

CONVERT(VARCHAR(11),@d,101)

OR

CONVERT(VARCHAR(25),@d,126)

This already converts the date into the format DavidG posted there.

Also, to double check if the query is coming out correctly, we assign the query text into a variable and then we use the print to show the variable, that print which is just simple text that will show on your Message tab alongside your resultset tab. As long as the Where clause shows with only two single quotations, the query you have should work, just in case of doubt, copy the message and run it separately by replacing all doubled single quotations into one single quotation only.

What I had in my message tab on WHERE clause was something like that:

WHERE thisdate BETWEEN ''02/27/2017'' AND ''2017-02-27T23:59:59.990''
NETRookie
  • 86
  • 6