How can I filter a SOQL query in a Python script after a specific datetime?

Question

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:

• https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

• https://salesforce.stackexchange.com/questions/301323/passing-a-variable-in-soql-select-statement-with-python-and-simple-salesforce

• https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#format_datetime

Answer 1

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)