SOQL Query for month operator

Question

I have to compare month value of a custom object's date field, if it is of the current month. Please find the piece of code below. I am getting error here. Can someone help me?

Code piece:

for(Call2_vod__c call: [SELECT Id,Account_vod__c, Call_Date_vod__c, Status_vod__c, Detailed_Products_vod__c, CreatedBy.Primary_Territory_vod__c
     FROM Call2_vod__c 
     WHERE 
     Month(Call_Date_vod__c) = :today.Month()
     and Detailed_Products_vod__c != null 
     and Detailed_Products_vod__c like 'SAMSCA%'
     and Status_vod__c = 'Submitted_vod' 
     and Account_vod__c IN: hcpAccountmap.Values()])

Error displayed,

Error: Compile Error: Invalid aggregate function: Month at line 43 column 29

Answer 1

Month is apex function and will not work in SOQL. You should use CALENDAR_MONTH

Use query like bellow

select id,name from contact where  CALENDAR_MONTH(createddate) = 8 limit 10 

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_date_functions.htm

I guess your code will look like this:

Integer Int_Month = Date.today().month();
for(Call2_vod__c call: [SELECT Id,Account_vod__c, Call_Date_vod__c, Status_vod__c, Detailed_Products_vod__c, CreatedBy.Primary_Territory_vod__c
 FROM Call2_vod__c 
 WHERE 
 CALENDAR_MONTH(Call_Date_vod__c) = :Int_Month 
 and Detailed_Products_vod__c != null 
 and Detailed_Products_vod__c like 'SAMSCA%'
 and Status_vod__c = 'Submitted_vod' 
 and Account_vod__c IN: hcpAccountmap.Values()])

Answer 2

it is working by this.

for(Call2_vod__c call: [SELECT Id,Account_vod__c, Call_Date_vod__c, Status_vod__c, Detailed_Products_vod__c, CreatedBy.Primary_Territory_vod__c FROM Call2_vod__c WHERE Call_Date_vod__c = this_month and Detailed_Products_vod__c != null and Detailed_Products_vod__c like 'SAMSCA%' and Status_vod__c = 'Submitted_vod' and Account_vod__c IN: hcpAccountmap.Values()])

Thanks all of you!!