SalesForce ADO.NET Source in SSDT 2013 SOQL Statement Using Date Literals Not Returning Results

1
votes

I am having an issue with a date literal not returning any results when I run an SOQL query. The query is as follows:

Select * From dbo.Case WHERE CreatedDate = YESTERDAY

With the query, I would like to obtain case data from the previous day. I know there is data available that was created the previous day. The results of the query when I preview it, though, are an empty set with no error message.

A different wrinkle that makes this not quite a strict SOQL issue is that I am trying to use this query as an SQL command on an ADO.NET connection using the CData ADO.NET driver to connect to a SalesForce.com instance. My goal is to be able to build SSDT packages that will allow me to stage the data from SalesForce into our SQL Server for processing there.

I have similar issues using the LAST_N_DAYS date literal as well.

I believe I should be using SOQL to query in the SQL command text field for the ADO.NET source connection but I am not 100% sure about that. I know for certain that I cannot use T-SQL because it does not recognize the GETDATE().

Any guidance on how to pull the records from Case for the previous day or where the query I am using might be wrong would be greatly appreciated.

1
datesalesforcesql-server-data-toolssoql

1 Answers

1
votes

Found an answer. The following SQL command will pull the data from the previous day:

Select * From dbo.Case Where CreatedDate = 'YESTERDAY'

The single quotes evaluate the yesterday date literal as expected.

Likewise, the following SQL statement will get the last 30 days of data.

Select * From dbo.Case Where CreatedDate = 'LAST_N_DAYS:30'

Thanks to anyone who researched and attempted the question! :)