javascriptnode.jsdatesalesforcejsforce

Fail to query by a SalesForce Date object using jsforce


I'm using jsforce find-api in order to search for an account by DOB which is a field of type Date.

The call to the API looks like this:

const conn = await connection();
result = await conn.sobject(this.schema.Name).find(rawSObject, Object.keys(this.schema.Fields));

where rawSObject looks like the following:

Try 1

const rawSObject = { [AccountFields.PersonBirthdate]: moment(birthDate, 'MM/DD/YYYY').format('YYYY-MM-DD') };

but it fails with:

INVALID_FIELD: 
 PersonBirthdate = '1985-12-12'
                                      ^
ERROR at Row:1:Column:985
value of filter criterion for field 'PersonBirthdate' must be of type date and should not be enclosed in quotes

Try 2

const rawSObject = { [AccountFields.PersonBirthdate]: moment(birthDate, 'MM/DD/YYYY').toISOString();

but still getting the same error.

Try 3

I also tried passing a date object:

const rawSObject = { [AccountFields.PersonBirthdate]: moment(birthDate, 'MM/DD/YYYY').toDate()

and this time I got a different error:

MALFORMED_QUERY: 
PersonBirthdate = Thu Dec 12 1985 00:00:00 GMT+0200
                               ^
ERROR at Row:1:Column:1002
Bind variables only allowed in Apex code
    at HttpApi.getError (/Users/nalfasi/dev/salesforce-gateway/node_modules/jsforce/lib/http-api.js:250:13)...

Try 4

While searching the web, I also ran into the following thread: https://github.com/jsforce/jsforce/issues/851 and tried the suggestion there, specifically the one in the comment here: https://github.com/jsforce/jsforce/issues/851#issuecomment-594233217

rawSObject = { [AccountFields.PersonBirthdate]: { $eq: SfDate.toDateTimeLiteral(birthDateObj) };

and now I got a new error:

INVALID_FIELD: 
 PersonBirthdate = 1985-12-11T22:00:00Z
                                      ^
ERROR at Row:1:Column:985
value of filter criterion for field 'PersonBirthdate' must be of type date and should not be enclosed in quotes
    at HttpApi.getError (/Users/nalfasi/dev/salesforce-gateway/node_modules/jsforce/lib/http-api.js:250:13)

Further

I also searched the web and found questions such as: Cannot Update Custom Date field via SalesForce API (JSforce)

and jsforce query method chain to find data between 2 dates

and https://salesforce.stackexchange.com/questions/22851/trouble-using-soql-to-filter-results-by-a-range-of-dates-what-is-the-correct-s

and https://salesforce.stackexchange.com/questions/8896/using-a-date-for-a-datetime-field-in-a-soql-query

but they also weren't helpful


Solution

  • The mistake was a combination of treating DateTime as a Date object together with lack of examples in the documentation of jsforce.

    Finally I was able to figure it out:

    const birthDateObj = moment(birthDate, 'MM/DD/YYYY').toDate();
    rawSObject = { [AccountFields.PersonBirthdate]: { $eq: SfDate.toDateLiteral(birthDateObj) };