Add a PreSQL Filter to an ODBC Connection

Question

I am trying to add a filter on a DateTime field within an Input connection using the ODBC connector for Salesforce to limit the result coming into the workflow.  When I try to add a filter I keep getting an error stating the syntax is incorrect, but I am not sure why.  The format of the DateTime field in Salesforce is 'YYYY-MM-DDT00:00:00Z' and I tried using Select From [Table] WHERE CreatedDate <  '2020-01-01T00:00:00Z' but no luck.  I have also tried multiple varations of an address with the same results.

If I use the Salesforce Input connector I can use a Where clause of LAST_N_YEARS:2 which works in a sandbox, but in our production org the table is too large and times out when running so I have to use the ODBC connection.  This does not work in the ODBC connection.

Does anyone know what the datetime format needs to be for this connection and how to configure it?  For reference the driver is a Simba driver.

BobR · Answer

I'm a heavy user of salesforce data and don't see anything fundamentally wrong with your date format. Salesforce reference is here https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm and in there they provide the following example which looks aligned to your approach. The only real difference is that their example doesn't use quotes around the date. So I'd try removing those.

SELECT Id FROM Account WHERE CreatedDate > 2005-10-08T01:02:03Z

Another approach you can explore is calling their API directly and bypassing the odbc call or the Alteryx Tool. Their API is pretty standard REST API. You can put it in an iterative macro with the download tool. The approach we took was to use the python tool mainly because it was easier to do exception handling in addition we can use the threading library and pull the data faster. We had one pull that used the native alteryx salesforce tool and pulled roughly 2 million rows 70 columns wide in 3 hours. With our new macro running 8 threads we got that down to 8 minutes.

Bob