I'm trying out the Salesforce Simba ODBC connection but I having trouble adding date filters to the where clause in the sql statement. I'm using an Input Data tool and using the Salesforce ODBC connection through DCM.
These pass the SOQL/SQL validation but I get "invalid character value for cast specification" when I try to execute it:
where LastModifiedDate > '2024-06-20T01:00:00Z'
where LastModifiedDate > 'YESTERDAY'
These give me a SOQL validation error:
where LastModifiedDate > 2024-06-20T01:00:00.000Z
where LastModifiedDate > 2024-06-20T01:00:00Z
where LastModifiedDate > YESTERDAY
If I remove the "where" and add "TOP 1000" to the select then it returns records successfully. Has anyone had any success filtering by dates in the tool sql?
EDIT - I finally got one to work with just a date and not the time (the LastModifiedDate is a DateTime field):
where LastModifiedDate > '2024-06-20'
But with SOQL you're supposed to be able to be able to use literals like "YESTERDAY' or "LAST_WEEK" and I haven't figured out a way to do that.
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sel...
