0
SELECT Id, Last_stage_changed_date__c, CreatedDate 
FROM Opportunity 
WHERE Last_stage_changed_date__c < CreatedDate

Last_stage_changed_date__c is of the Date type. I feel like I'm doing something dumb but I can't spot it.

Getting the error:

Bind variables only allowed in Apex code

  • Seems a weird query logically: surely CreatedDate comes before any other date? – Keith C May 14 '19 at 17:47
  • I was trying to use this query to fix a bug where this is the case – Josh May 14 '19 at 18:13
  • Fair enough. I'd add the formula, fix the data, then remove the formula. – Keith C May 14 '19 at 18:32
5

You cannot compare one field to another in SOQL, regardless of context. If you want to compare a custom date field to CreatedDate, you will need a separate checkbox formula field to do the comparison for you, then you can filter on this field being true.

Take a look at the documentation on fieldExpression Syntax (emphasis mine):

fieldName comparisonOperator value

value: A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.

  • Is there a way to do this in Apex code in that case? I don't want to create any additional fields if I don't have to. – Josh May 14 '19 at 17:39
  • 2
    Nope. Unless you want to query every record in the system and then post-process. No matter what your context, comparing one field to another is not supported, except by adding a formula. – Adrian Larson May 14 '19 at 17:48

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.