3

The below query is not working:

SELECT Id
FROM OrderItem
WHERE CALENDAR_MONTH(Order.EffectiveDate) = THIS_MONTH
AND CALENDAR_YEAR(Order.EffectiveDate) = THIS_YEAR

I am getting the following error message:

INVALID_FIELD: WHERE CALENDAR_MONTH(Order.EffectiveDate) = THIS_MONTH ^ ERROR at Row:3:Column:22 value of filter criterion for expression CALENDAR_MONTH(EffectiveDate) must be of type integer and should not be enclosed in quotes

What am I doing wrong?

3

You can do either:

CALENDAR_MONTH(Field__c) = 7

or:

Field__c = THIS_MONTH

You want the latter.

3

To add to Adrian's answer, the documentation explains that:

You can't compare the result of a date function with a date literal in a WHERE clause. The following query doesn't work:

SELECT CreatedDate, Amount
FROM Opportunity
WHERE CALENDAR_YEAR(CreatedDate) = THIS_YEAR

hence the need for a workaround where you have to add a field containing the month.

2

Please explore Date Functions for SOQL. CALENDAR_MONTH() returns a value of the Integer type, whereas THIS_MONTH (see Date Formats and Date Literals) is a String literal.

So basically what you get in the runtime for CALENDAR_MONTH(Order.EffectiveDate) = THIS_MONTH is (don't mind the actual values, they are for the example's sake only):

7 = '2005-10-08T01:02:03Z'

So you need to follow Adrian's advice.

1

CALENDAR_MONTH/CALENDA_YEAR returns a number. Here, you are trying to compare a number to a date, that is why, it throws an error.

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.