Salesforce Simba ODBC - specify date in Where clause

[sd4w95sf8e]

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...

[francois45p]

Hi @sd4w95sf8e ,

I have the same issue. The Simba SF ODBC driver is supposed (default parsing configuration) to parse SOQL queries first. So I don't understand why date literals like 'TODAY' or 'YESTERDAY' do not work.

I also tried using SQL-92 date functions (which should also be compatible with Simba driver) but it does not work so far.

Anyone successfully query Salesforce ODBC with advanced where clauses using dates?

[francois45p]

I could not manage to use the functions DATEDIFF and DATEADD but instead I was successful to do the following:

to search for records within :

• the last n days, you can simply use the difference of the dates like "where CURDATE() - Opportunity.LastModifiedDate < n"
• the current year, you can use "where DAYOFYEAR(Opportunity.LastModifiedDate) = DAYOFYEAR(CURDATE())" 
• the last n months, you can use "where mod(MONTH(CURDATE()) - MONTH(Opportunity.LastModifiedDate) + 12, 12) + 1 <= n"

Note that although it runs well in Alteryx, when you open the SQL editor in Alteryx, I have an error indicating the syntax is wrong.