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
RDM1961RDM1961 

Compare two date fields in SOQL

I'm trying to do an update in salesforce using Informatica.  The source object has multiple date fields which I'm trying to compare to select records. I get a Malformed Query error when attempting to run the update.

The query statement that informatica generates is below.

 

SOSQL [Select Discharge_Date__c, Implant_Date__c, Patient__c From Implant__c Where ((((Implant_Date__c > Pts_last_fu_date__c) or (Discharge_Date__c > Pts_last_fu_date__c)) and Patient__c = 'a0630000007ewCc')) LIMIT 10]

  

Any help would be appreciated,

 

Thanks,

Rick

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

You can't compare one field against another only against a literal value. To do this, you first need to create a new formula field that calculates the difference between the 2 dates, then you can write a soql query that filters on that date difference formula field value.

All Answers

AtulRAtulR

I think you should use DATEVALUE function around your date column .. something like this..

SOQL

 

 [Select Discharge_Date__c, Implant_Date__c, Patient__c From Implant__c Where ((((DATEVALUE(Implant_Date__c) > DATEVALUE(Pts_last_fu_date__c)) or (DATEVALUE(Discharge_Date__c) > DATEVALUE(Pts_last_fu_date__c))) and Patient__c = 'a0630000007ewCc')) LIMIT 10]

If you face any issue then feel free to reply back if this work the please mark this as solved.

Thank you,
Atul
Pradeep_NavatarPradeep_Navatar

you can use daysbetween method to compare your date fields :

 

date startDate = date.newInstance(2008, 1, 1);

date dueDate = date.newInstance(2008, 1, 30);

integer numberDaysDue = startDate.daysBetween(dueDate);  

 

Hope this helps.

 
SuperfellSuperfell

You can't compare one field against another only against a literal value. To do this, you first need to create a new formula field that calculates the difference between the 2 dates, then you can write a soql query that filters on that date difference formula field value.

This was selected as the best answer
RickMetheRickMethe

Thanks for all the suggestions. Here's the final resolution in case it may help anyone else.

 

Case:

Patient tracking application has Last Follow Up Date on patient record. Needs to update a this date field from one of two date fields in a child object (Implant) if greater than current Last Follow Up Date.

 

Solution:

 

  • Created formula field (ChkLastFUDate)on Implant  to compare child date fields and current Last Follow Up Date

 

If(or( isnull(Patient__r.Last_Follow_up_Date__c), Implant_Date__c > Patient__r.Last_Follow_up_Date__c , Discharge_Date__c > Patient__r.Last_Follow_up_Date__c),'True','False')

 

 

  • Created Workflow Rule that fires if ChkLastFUDate = 'True'
  • Created Field Update to update Last Follow Up Date. Use formula to get larger of Implant or Discharge date:
IF(or(Isnull(Discharge_Date__c), Implant_Date__c > Discharge_Date__c) , Implant_Date__c, Discharge_Date__c)
 
Also used the ChkLastFUDate in informatica in one time process to update existing records. 
 
Thanks again for the suggestions. 
Rick