1

I'm trying to select all records from the OPPORTUNITY object that are greater than a variable DateTime. However, I can't figure out how. This is in a Python script using the simple_salesforce package. I believe the issue is either that I am missing the millisecond and +0000 timezone specification in my "max_date" parameter, which I retrieve from a BigQuery table, or that I'm not passing in my max_date parameter as a correct datatype.

My example code that does not work:

max_date = '2020-08-11T17:41:29'

SF_QUERY = ("""
  SELECT Id,
  CreatedDate
  FROM Opportunity
  WHERE CreatedDate > %s
  """ % max_date)

Where the CreatedDate field is formatted like so:

2019-10-31T16:01:19.000+0000

The query returns the error

 Response content: [{'message': "line 8:57 no viable alternative at character '<EOF>'", 'errorCode': 'MALFORMED_QUERY'}]

If I add quotes around the %s, to make it '%s', then I get the error

Response content: [{'message': "\n  AND BU_Last_Stage_Changed_Date__c >\n      ^\nERROR at Row:8:Column:7\nvalue of filter criterion for field 'BU_Last_Stage_Changed_Date__c' must be of type dateTime and should not be enclosed in quotes", 'errorCode': 'INVALID_FIELD'}]

Thanks for any help.

Referenced documentation:

Lee Werner
  • 167
  • 7
  • From the last link you posted, it seems the datetime should be '2020-08-11 17:41:29' – Oin Aug 12 '20 at 16:22
  • That format doesn't work, either as a string or datetime. I tried passing it into the SOQL query and received the same error. This was the format I used: ```2020-08-11 17:41:29``` `````` – Lee Werner Aug 12 '20 at 16:38
  • 1
    or https://stackoverflow.com/q/63081281/313628 – eyescream Aug 12 '20 at 16:58
  • Thanks eyescream, that does. I actually just found that solution myself minutes before you commented from the post https://codecracksblog.wordpress.com/2016/09/15/how-to-use-datetime-in-dynamic-soql-query-where-condition/. I simply needed to append a "Z" onto the end of my datetime string for SOQL to use it in the WHERE clause. – Lee Werner Aug 12 '20 at 17:09

1 Answers1

0

The solution is to append a "Z", short for Zulu time, which indicates UTC, to the end of my datetime string. The dynamic variable %s should not have quotes. Example:

max_date = '2020-08-10T17:41:29' # in prod, pull this data from BigQuery
max_date = max_date + "Z"

SF_QUERY = ("""
  SELECT Id,
  CreatedDate
  FROM Opportunity
  WHERE CreatedDate > %s
  """ % max_date)
Lee Werner
  • 167
  • 7