0

I am working on an API end point that returns data and allows user to pass in a 'createdDateStart' to filter records on a Formula field called Formatted_Created_Date__C. The contents of the formula field are as follows:

LPAD(TEXT(MONTH(DATEVALUE(CreatedDate))), 2, "0") + "/" +
LPAD(TEXT(DAY(DATEVALUE(CreatedDate))), 2, "0") + "/" +
LPAD(TEXT(YEAR(DATEVALUE(CreatedDate))), 4, "0")

On the UI user enters '2020-03-23'

On the API I process the input as:

String createdDateStartStr = request.params.get('createdDateStart');
Date createdDateStart;
if (!String.isBlank(createdDateStartStr )){
createdDateStart = Date.valueOf(createdDateStartStr);
}

On the actual SOQL query in the end point I am doing: 'AND Formatted_Created_Date__C >= :createdDateStart'.

I am getting the error:

Invalid Bind Expression type of Date for column of type String

One thing I could do is to use CreatedDate instead of Formatted_Created_Date__C and that works fine. However what can I do to make it work for Formatted_Created_Date__C ?

Many thanks. Trying to better understand.

1

Formatted_Created_Date__c is a String return type, so you need to match this type. In other words, you need to format the date as it would appear in the formula.

P.S. You should use CreatedDate, not Formatted_Created_Date__c, because the latter uses a full table scan and may take significant time to filter, while using CreatedDate can leverage an index and is very fast.

1
  • thanks as always sfdcfox. That is what I was leaning towards also and your explanation makes perfect sense.
    – kratos
    Jun 5 '20 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.