function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DBManagerDBManager 

Apex Trigger SOQL query comparison of dates by month

I'm trying to do something really basic, but I can't seem to get it right. 

 

Here is my existing code:

trigger UpdateGBPAmount on Item__c (after insert, after update) {

for (integer i=0; i < Trigger.new.size(); i++){
    if (Trigger.new[i].RecordTypeId == '012R00000004ni3' || Trigger.new[i].RecordTypeId == '01220000000AGp1'){
        Exchange_Rate__c[] rate = [SELECT Id, Date__c, Rate__c FROM Exchange_Rate__c WHERE Name LIKE 'USD%'];
    }
}

}

To the above SOQL query, I want to add a condition to check that the Exchange Rate Date__c field is in the same month and year as the Item Deal_Date__c field. So something like:

 

AND month(Date__c) = month(Trigger.new[i].Deal_Date__c) AND year(Date__c) = year(Trigger.new[i].Deal_Date__c)

 But if try the above, or variations I have found in reasearching this problem, none work.

 

Can anyone help?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

You can't do that sort of thing in SOQL I'm afraid.  The left hand side of the operator can only be a field name, so you can't do things like month(Date__c).  The way to do this is to invert the logic and compare the Date__c field with a date range.  Something like:

 

Date startDate=trigger.new[i].Deal_Date__c.toStartOfMonth();
Date endDate=i.Deal_Date__c.toStartOfMonth().addMonths(1);

...

AND Date__c >= :startDate AND Date__c<endDate

 

All Answers

bob_buzzardbob_buzzard

You can't do that sort of thing in SOQL I'm afraid.  The left hand side of the operator can only be a field name, so you can't do things like month(Date__c).  The way to do this is to invert the logic and compare the Date__c field with a date range.  Something like:

 

Date startDate=trigger.new[i].Deal_Date__c.toStartOfMonth();
Date endDate=i.Deal_Date__c.toStartOfMonth().addMonths(1);

...

AND Date__c >= :startDate AND Date__c<endDate

 

This was selected as the best answer
nickwick76nickwick76

Perhaps you should consider moving the SOQL-statement out of the for-loop as well and add to a collection instead (although this is not related to the question).

 

If you have many updates and inserts that matches the recordtype conditions you could run into exception concerning governor limits.

 

Br / Niklas
DBManagerDBManager

Many thanks, that seemed to work. I figured setting variables would be the way to go, but couldn't get it to work.

 

Here is my finished query:

Date startDate=trigger.new[i].Deal_Date__c.toStartOfMonth();
Date endDate=Trigger.new[i].Deal_Date__c.toStartOfMonth().addMonths(1);
        
        Exchange_Rate__c[] rate = [SELECT Id, Date__c, Rate__c FROM Exchange_Rate__c WHERE Name LIKE 'USD%' AND Date__c >= :startDate AND Date__c < :endDate LIMIT 1];