Date / Datetime fields in find return "INVALID_FIELD"

[cameronwiebe]

When trying to look up a contact based on their FirstName, LastName and Birthdate, Salesforce returns an error. If I omit Birthdate, the find function works as expected but when I include it I receive an "INVALID_FIELD" error. Here is what the code looks like:

conn.sobject("Contact").find({ "FirstName":"John", "LastName":"Smith", "Birthdate":"1983-09-28T07:00:00.000Z" }, "Id, CreatedDate, LastModifiedDate").execute(function (err, records) { if (err) { return { records: false, err: err }; } else { return { records: records, err: false }; } })

The error looks like:

{"name":"INVALID_FIELD","errorCode":"INVALID_FIELD"}

Does anyone know why this might be happening?

[athenasumit]

Me too encountered with the same problem. Looks like when it converts in SOQL, it encloses date in commas which is not allowed in SOQL queries. This is a bug. Inspite of find you can use conn.query and write native SOQL query to do your stuff. I don't expect such a bug with such a popular library.

[simmondsdt]

Has anyone found any other solution to this other than raw SOQL? Having the ability to to use JSON was scalable and a huge plus until I just found out I can't use dates.

As an alternative, I thought I could use the built in jsforce date library. In the jsforce website sandbox, I am able to use this conn.sobject("Lead").find({ Last_Call__c: jsforce.Date("2020-05-13T00:00:00.000Z") }) and it works perfectly. Trying to use that exact same code in my node api fails and returns undefined, anyone know why?

[cristiand391]

triaging old issues...

there's some utilites in jsforce to parse date strings, see: https://jsforce.github.io/jsforce/classes/date.SfDate.html

I've the example above with jsforce v3:

const jsforce = require('./lib/index');

const conn = new jsforce.Connection({});

(async() => {

  const soqlQUery = await conn.sobject("Contact").find({
    "FirstName":"John",
    "LastName":"Smith",
    "Birthdate":jsforce.SfDate.toDateTimeLiteral("1983-09-28T07:00:00.000Z")
  }, "Id, CreatedDate, LastModifiedDate").toSOQL()
  console.log(soqlQUery)
})()

which logs

SELECT Id, CreatedDate, LastModifiedDate FROM Contact WHERE FirstName = 'John' AND LastName = 'Smith' AND Birthdate = 1983-09-28T07:00:00Z

labeling as docs to document these helpers.

[cristiand391]

added a Dates section here with some examples of the helpers provided by jsforce: https://jsforce.github.io/document/#using-query-method-chain