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?

4 Answers 4

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.

3

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .